pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTrouble w/ COPY command
3+ messages / 3 participants
[nested] [flat]
* Trouble w/ COPY command
@ 2012-04-14 22:45 Wells Oliver <wellsoliver@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Wells Oliver @ 2012-04-14 22:45 UTC (permalink / raw)
To: pgsql-admin
I have CSV files blowing up because they have double quotes around numeric
values: " 1.34" yields ERROR: invalid input syntax for type numeric: "
1.34"
And occasionally these are empty, and you see invalid syntax for type
numeric: " "
Are there flags I can pass to COPY to avoid this? There are properly quoted
string values elsewhere in the final, so I'm reticent to strip out double
quote characters or anything.
Thank you.
--
Wells Oliver
wellsoliver@gmail.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trouble w/ COPY command
@ 2012-04-14 23:17 Greg Williamson <gwilliamson39@yahoo.com>
parent: Wells Oliver <wellsoliver@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Williamson @ 2012-04-14 23:17 UTC (permalink / raw)
To: Wells Oliver <wellsoliver@gmail.com>; pgsql-admin
Wells Oliver asked:
>
>I have CSV files blowing up because they have double quotes around numeric values: " 1.34" yields ERROR: invalid input syntax for type numeric: " 1.34"
>>
>And occasionally these are empty, and you see invalid syntax for type numeric: " "
>
>Are there flags I can pass to COPY to avoid this? There are properly quoted string values elsewhere in the final, so I'm reticent to strip out double quote characters or anything.
>
There's no easy command line method that I know of. Perhaps if you can detect patterns some sed / awk might help.
I'd probably load the CSV file into a temporary work table with the columns that are giving you issues defined as type "text." Then use SQL to remove the offending quotation marks from the afflicted columns. Create new columns of type numeric in the table, and copy the values from the text variant to the numeric ones with the proper cast one at a time; if it blows up you go back to resolving issues. Then copy the table using the numeric columns to the desired destination.
Hopefully others might have better ideas ... best o' luck!
Greg Williamson
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trouble w/ COPY command
@ 2012-04-15 00:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wellsoliver@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2012-04-15 00:01 UTC (permalink / raw)
To: Wells Oliver <wellsoliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wellsoliver@gmail.com> writes:
> I have CSV files blowing up because they have double quotes around numeric
> values: " 1.34" yields ERROR: invalid input syntax for type numeric: "
> 1.34"
Is that an exact copy of the error message? Because there are double
quotes in that error message's text; what you are showing does not
suggest that they are part of the data value. I'm wondering about
non-visible characters myself.
> And occasionally these are empty, and you see invalid syntax for type
> numeric: " "
Well, that you're going to have to do something else about. Possibly
you could treat the empty string as null? (See the 'null string'
option to COPY.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-04-15 00:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-14 22:45 Trouble w/ COPY command Wells Oliver <wellsoliver@gmail.com>
2012-04-14 23:17 ` Greg Williamson <gwilliamson39@yahoo.com>
2012-04-15 00:01 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox