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

On 10/16/2014 11:04 AM, Steve Wampler wrote:
> On 10/16/2014 09:42 AM, Rob Sargent wrote:
>> On 10/16/2014 10:33 AM, Steve Wampler wrote:
>>> This is with Postgresql 9.3.5.
>>>
>>> I'm looking at using a COPY command (via jdbc) to do bulk inserts
>>> into a table that
>>> includes a BIGSERIAL column. Is there a way to mark the data in that
>>> column so it gets assigned a new value on entry - akin to the use of
>>> 'default'
>>> in an INSERT? Some of the rows have values for the serial column,
>>> others
>>> don't.
>>>
>>> Or is the only way to use COPY for this task:
>>>
>>> COPY table_name (columnnameA, columnnameB, columnnameD) FROM source;
>>>
>>> where the serial column name is omitted? This wouldn't preserve the
>>> values
>>> for the serial column on rows that have one already.
>>>
>> Doesn't this guarantee collision at some point?
>
> Depends - without the UNIQUE tag on that column it shouldn't matter.
> Or, with a bigserial there's a lot of room to play with. The rows
> with existing
> serial values might all have negative values for that column, for
> example.
>
>> I might add a column to the target table which would contain the
>> "foreign" serial id and give all records the "local"
>> serial. Update local to foreign iff safe and desired.
>
> I don't think this addresses the problem of having entry rows with no
> serial column in them.
No data in the column (null) or no column at all?

I appreciate the vastness of bigserial but I think it starts at 1. Are
negative numbers even allowed? To clarify my suggestion: all incoming
records would get a new "local" big serial and those incoming records
WITH a value would set the "foreign" bigserial though that column would
have to be typed as bigint nullable (this would allow negative values).
That said, according to my test, the supplied bigserial value would get
insert as supplied if not null (without the extra column I suggested)
My test

postgres=# create table t (id bigserial, name text);
CREATE TABLE
postgres=# insert into t values('rjs');
ERROR: invalid input syntax for integer: "rjs"
LINE 1: insert into t values('rjs');
^
postgres=#
postgres=# insert into t (name) values('rjs');
INSERT 0 1
postgres=# select * from t;
id | name
----+------
1 | rjs
(1 row)

postgres=# insert into t (id, name) values(777, 'rjs');
INSERT 0 1
postgres=# select * from t;
id | name
-----+------
1 | rjs
777 | rjs
(2 rows)

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