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