Re: COPY CSV keywords - Mailing list pgsql-hackers

From Andrew Dunstan
Subject Re: COPY CSV keywords
Date
Msg-id 40853BF8.4060304@dunslane.net
Whole thread Raw
In response to COPY CSV keywords  (Bruce Momjian <pgman@candle.pha.pa.us>)
List pgsql-hackers
I wrote:

>
> 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.


The other alternative for the NOT NULL side would be to abandon it and 
tell users they would need to use a trigger. That requires a little more 
work from them, but would work in the general case, whereas this one is 
likely to fail on everything but a text-and-friends column. Example:

andrew=# create table b(a int not null default 0);
CREATE TABLE
andrew=# create function bnull() returns trigger language plpgsql as $$
andrew$# begin
andrew$# if NEW.a is null then NEW.a := 0; end if;
andrew$# return NEW;
andrew$# end;
andrew$# $$;
CREATE FUNCTION
andrew=# create trigger btrigger before insert on b FOR EACH ROW EXECUTE 
PROCEDURE bnull();
CREATE TRIGGER
andrew=# copy b from stdin csv;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.>>>> \.
andrew=# select * from b
andrew-# ;a
---0
(1 row)


But there isn't a reasonable alternative to making the user make choices 
on the output side (see previous discussion regarding zip codes).

cheers

andrew


pgsql-hackers by date:

Previous
From: Rod Taylor
Date:
Subject: Re: ERROR action extension for rules?
Next
From: Dave Cramer
Date:
Subject: pl/j looking for alpha testers