From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Hans Edwin Winzeler <hewinzeler(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Invalid input syntax for integer |
Date: | 2011-08-18 15:55:01 |
Message-ID: | 23470.1313682901@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hans Edwin Winzeler <hewinzeler(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Pushpendra Singh Thakur | 2011-08-20 09:33:38 | Re: How to restore an entire server from a .sql file created with PGAdmin Backup Server? |
Previous Message | Jean-Yves F. Barbier | 2011-08-18 15:43:04 | Re: Invalid input syntax for integer |