Postgres Pro
Downloads
Home   >   mailing lists

Thread: What am I doing wrong with this comma-delimited copy?

What am I doing wrong with this comma-delimited copy?

From
James Long
Date:
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

james=> \pset fieldsep ,
Field separator is ",".
james=> \copy transactions from bar
\.
ERROR:  value too long for type character(14)
CONTEXT:  COPY transactions, line 1, column symbol: "Cash,0,0,Cash Adjustment,4/2/2007,,,0.87,0,0,         ,BALANCE-SWP
T1TO T2,16,,Financial" 


psql appears to be treating the entire input line as one tuple,
instead of seeing the fieldsep value as a field delimiter.

Also, one issue I can see coming is the date format.  Is there
a way I can read M-D-Y (or M/D/Y) format dates and store them?
My default postgresql date format is Y-M-D.  I'd be happy to
retain Y-M-D on display, I just don't want to have to involve
a script to massage the input dates to Y-M-D format.


Thank you!


Jim

Re: What am I doing wrong with this comma-delimited copy?

From
Sean Davis
Date:
James Long wrote:
> Welcome to psql 8.1.8, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help with psql commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> james=> \pset fieldsep ,
> Field separator is ",".
> james=> \copy transactions from bar
> \.
> ERROR:  value too long for type character(14)
> CONTEXT:  COPY transactions, line 1, column symbol: "Cash,0,0,Cash Adjustment,4/2/2007,,,0.87,0,0,
,BALANCE-SWPT1 TO T2,16,,Financial" 
>
>
> psql appears to be treating the entire input line as one tuple,
> instead of seeing the fieldsep value as a field delimiter.
>
>
See here:

http://www.postgresql.org/docs/8.2/static/sql-copy.html

fieldsep does not affect the COPY command.

Re: What am I doing wrong with this comma-delimited copy?

From
Tom Lane
Date:
James Long <pgsql-novice@museum.rain.com> writes:
> james=> \pset fieldsep ,
> Field separator is ",".
> james=> \copy transactions from bar

> psql appears to be treating the entire input line as one tuple,
> instead of seeing the fieldsep value as a field delimiter.

You're confusing psql's output field separator with what COPY will use.
IIRC you need a "delimiter ','" clause on the copy command.

> Also, one issue I can see coming is the date format.  Is there
> a way I can read M-D-Y (or M/D/Y) format dates and store them?

See datestyle.

            regards, tom lane