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

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