Обсуждение: Invalid input syntax for integer

Hello,

I am new to PostgreSQL and have a data format issue that I can't resolve.

The input table from my .csv file (about 10 million records) that I am importing into PostgreSQL from a different program gives numbers in a format as follows, for example: 0-1788.000000 to indicate the value -1788. PostgreSQL gives me the error "invalid input syntax for integer" when I try to import the number (or invalid for numeric, real, double precision, etc. when I try to import it in those other formats). When I specify varchar(20) I get no problems and can import it, but then I can't use it numerically. How do I either:

• Import the value as NUMERIC or INTEGER or REAL or FLOAT or something that I can use as a value rather than text, OR
• How do I convert the varchar(20) that I have already imported into a numeric value?

Thanks

Edwin Winzeler

Hans Edwin Winzeler <hewinzeler@gmail.com> writes:
> I am new to PostgreSQL and have a data format issue that I can't resolve.

> The input table from my .csv file (about 10 million records) that I am
> importing into PostgreSQL from a different program gives numbers in a format
> as follows, for example: 0-1788.000000 to indicate the value -1788.
> PostgreSQL gives me the error "invalid input syntax for integer" when I try
> to import the number (or invalid for numeric, real, double precision, etc.
> when I try to import it in those other formats). When I specify varchar(20)
> I get no problems and can import it, but then I can't use it numerically.
> How do I either:


>    - Import the value as NUMERIC or INTEGER or REAL or FLOAT or something
>    that I can use as a value rather than text, OR
>    - How do I convert the varchar(20) that I have already imported into a
>    numeric value?

Well, somehow you're going to have to get rid of that bizarre leading
zero.  The best way would be to fix the other program to not output such
a stupid data format.  If that's not feasible, you could fix the
intermediate text file using something like sed, or you could fix the
data after-the-fact in Postgres.  The usual way to do the latter is to
import the data initially into a temporary table that has a text column
to receive the weird data, and then use INSERT...SELECT... to transform
the data and insert it into the final table.  In this case you'd want to
use a function like regexp_replace() to delete the leading zero, and
then cast the result to a suitable numeric datatype.  So it'd look
something like

    INSERT INTO finaltable
    SELECT ..., regexp_replace(weirdcolumn, '^0-', '-')::numeric, ...
    FROM temptable;

            regards, tom lane

On Thu, 18 Aug 2011 11:07:58 -0400, Hans Edwin Winzeler <hewinzeler@gmail.com>
wrote:

> The input table from my .csv file (about 10 million records) that I am
> importing into PostgreSQL from a different program gives numbers in a format
> as follows, for example: 0-1788.000000 to indicate the value -1788.
> PostgreSQL gives me the error "invalid input syntax for integer" when I try
> to import the number (or invalid for numeric, real, double precision, etc.
> when I try to import it in those other formats). When I specify varchar(20)
> I get no problems and can import it, but then I can't use it numerically.
> How do I either:
>
>
>    - Import the value as NUMERIC or INTEGER or REAL or FLOAT or something
>    that I can use as a value rather than text, OR
>    - How do I convert the varchar(20) that I have already imported into a
>    numeric value?

AFAIK you can't without touching the CVS file.

Think a second: how can Pg (or any other app) recognize any number in
'0-1223...', which isn't a standard international notation convention?

--
When pleasure remains, does it remain a pleasure?

On Thu, 18 Aug 2011 11:07:58 -0400, Hans Edwin Winzeler <hewinzeler@gmail.com>
wrote:

Oops, forgot this one:

>    - How do I convert the varchar(20) that I have already imported into a
>    numeric value?

you need to:
* add a real/float/numeric/... wanted column to the table,
* use a function that:
     read a row,
     get the varchar(20) column,
     check for beginning with 0-[1-9],
     strip '0-' from the string,
     convert it into the wanted format,
     update table's real/float/numeric/... column with the result,
     loop until end of table.

--
Reality is for people who can't deal with drugs.
        -- Lily Tomlin