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

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.

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?

--
Steve Wampler -- swampler(at)noao(dot)edu
The gods that smiled on your birth are now laughing out loud.

--
This mail was scanned by a trial version of BitDefender
For more information please visit http://www.bitdefender.com/