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

Search Discussions

  • Tom Lane at Aug 18, 2011 at 3:55 pm

    Hans Edwin Winzeler 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
  • Jean-Yves F. Barbier at Aug 23, 2011 at 2:38 pm

    On Thu, 18 Aug 2011 11:07:58 -0400, Hans Edwin Winzeler 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?
  • Jean-Yves F. Barbier at Aug 23, 2011 at 3:48 pm
    On Thu, 18 Aug 2011 11:07:58 -0400, Hans Edwin Winzeler 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 18, '11 at 3:08p
activeAug 23, '11 at 3:48p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase