Re: [HACKERS] COPY CSV keywords

[Andrew Dunstan]

Bruce Momjian wrote:

[ discussion moved to hackers.]

Peter Eisentraut wrote:
 

Bruce Momjian wrote:
   

I have applied the attached patch that complete TODO item:

       o -Allow dump/load of CSV format

This adds new keywords to COPY and \copy:

       CSV - enable CSV mode
       QUOTE - specify quote character
       ESCAPE - specify escape character
       FORCE - force quoting of specified columns
     

FORCE QUOTE

   

       LITERAL - prevent NULL checks for specific columns
     

NO NULL CHECK
   

I considered going this direction, but it broke the WITH clause style of
COPY.  Previously it was "WITH keyword value".  Now it is also "WITH
keyword value, value" too.  This would add "WITH keyword keyword value,
value".
It would change:
	
	COPY pg_language TO '/tmp/x' WITH CSV FORCE lanname 
		LITERAL lanacl
to:
	COPY pg_language TO '/tmp/x' WITH CSV FORCE QUOTE lanname NO
		NULL CHECK lanacl

If folks like it, I can make the change.

 

[reposting my comments from patches]

The problem is that using QUOTE or NULL in these phrases might look 
confusing, e.g.
 COPY mytable TO 'mytable.csv' CSV QUOTE '"' FORCE QUOTE field1,field2;
 COPY mytable FROM 'mytable.csv' NULL '' CSV NO NULL CHECK field1,field2;

I also don't think NO NULL CHECK actually matches the use case for this 
very well (and I'm dubious about LITERAL too).  They both describe the 
actual behaviour, but not what you are trying to achieve. Essentially, 
this would be used when you have a field with a NOT NULL constraint, but 
the input CSV data stream has what would otherwise be considered nulls. 
(COPY itself will never produce such a CSV, as non-null values that 
resemble null are always quoted, but third party programs well might.) 
So an alternative might be FORCE NOT NULL, but for the previous 
consideration. Perhaps use of an optional preposition might make things 
slightly clearer, e.g.:

 COPY mytable TO 'mytable.csv' CSV QUOTE '"' FORCE QUOTE  IN 
field1,field2;
 COPY mytable FROM 'mytable.csv' NULL '' CSV FORCE NOT NULL IN 
field1,field2;

But it does start to look a little too much like COBOL .

So I'm interested to see if there are any other inspirations people have.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])