Re: COPY command - CSV files - Mailing list pgsql-hackers

Umberto Zappi wrote:
> I wish modify COPY command for support of CSV files (dump/load files in
> CSV format).
> I think this option is very important from import data from spreedsheet
> as OpenOffice/calc or M$/excel.
> I have found this task in TODO list, also.
> 
> I've begin my work, modify COPY syntax in:
> 
> COPY tablename [ ( column [, ...] ) ]
>     FROM { 'filename' | STDIN }
>     [ [ WITH ]
>           [ BINARY ]
>           [ OIDS ]
>           [ DELIMITER [ AS ] 'delimiter' ]
>           [ [ OPTIONALLY ] ENCLOSED [ BY ] 'delimiter1' [ AND
> 'delimiter2' ] ]
>           [ NULL [ AS ] 'null string' ] ]
> 
> COPY tablename [ ( column [, ...] ) ]
>     TO { 'filename' | STDOUT }
>     [ [ WITH ]
>           [ BINARY ]
>           [ OIDS ]
>           [ DELIMITER [ AS ] 'delimiter' ]
>           [ ENCLOSED [ BY ] 'delimiter1' [ AND 'delimiter2' ] ]
>           [ NULL [ AS ] 'null string' ] ]
> 
> Syntax is like to  control-file of Oracle's utility sql*load.
> Enclosed define the first and second  delimiters (if are different)
> which surround each field.
> The delimiters may be optionally if keyword exists.

I guess you didn't notice that the TODO item has a dash next it, meaning
it is done and will be in 7.5.  We didn't use Oracle's syntax, but we do
allow for the escape character in the quotes to be specified if different:COPY tablename [ ( column [, ...] ) ] ]' ]
FROM{ 'filename' | STDIN }elimiter' ] ...] ]    [ [ WITH ] S ] AS ] 'null string' ]' ]          [ BINARY ]  [ AS ]
'delimiter'] ...] ]          [ OIDS ] AS ] 'null string' ]' ]          [ DELIMITER [ AS ] 'delimiter' ] ...] ]
[NULL [ AS ] 'null string' ]' ]          [ CSV [ QUOTE [ AS ] 'quote' ] , ...] ]                [ ESCAPE [ AS ]
'escape']                [ FORCE NOT NULL column [, ...] ]    TO { 'filename' | STDOUT }delimiter' ]
 
COPY tablename [ ( column [, ...] ) ] ]' ]    TO { 'filename' | STDOUT }delimiter' ]    [ [ WITH ] S ] AS ] 'null
string']' ]          [ BINARY ]R [ AS ] 'delimiter' ]          [ OIDS ] AS ] 'null string' ]' ]          [ DELIMITER [
AS] 'delimiter' ]          [ NULL [ AS ] 'null string' ]' ]          [ CSV [ QUOTE [ AS ] 'quote' ]                [
ESCAPE[ AS ] 'escape' ]
 

One interesting idea we had was for ,, to be null, and ,"", to be a
zero-length string.  You can control that with FORCE NOT NULL,
meaning ,, is a zero-length string too.

To get the full details, see the current docs on the developers web
page.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073