Re: COPY data into a table with a SERIAL column?

[Rob Sargent <robjsargent@xxxxxxxxx>]

On 10/16/2014 11:38 AM, David G Johnston wrote:

Steve Wampler wrote

Let me generalize the problem a bit:  How can I specify that the default
value of a column
is to be used with a COPY command when some rows have values for that
column and
some don't?

If you provide a value for a column, including NULL, the default _expression_
is not evaluated.

COPY is dumb but fast.  If you need logic you need to add it yourself. 
Either before the copy or copy into a temporary UNLOGGED table and write
smart SQL to migrate from that to the live table.

You can also put smarts into a trigger.

Personally I would generally stage all the data then write two INSERT INTO
... SELECT statements; one for the known values and one where you omit the
column and let the system use the default.

David J.

 



--
View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-data-into-a-table-with-a-SERIAL-column-tp5823278p5823291.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Yeah, part three of my test proves his point:

postgres=# insert into t (id, name) values(null, 'rjs');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, rjs).