I'm running postgresql 7.0.3 on redhat 7.0.
COPY doesn't work optimally with the SERIAL data type. If one of your
fields (say the last) is a SERIAL data type, and you want the COPY
command to generate a (unique, incrementing) value for each record read
from a data input file, I don't know of a methodology to recommend to
you. Use of OID's seems the only workable means of auto-numbering rows.
However, using OID's has a few drawbacks. Mainly, if my postgres engine
has several databases installed into it (say, A and B), and I save both
(using COPY WITH OIDS), restore A (WITH OIDS), add a few new records to
A, then restore B (WITH OIDS), won't some of the restored OID's from B
collide with the new records' OID's in A?
Because of this and other drawbacks (eg, OID's are very large,
meaningless numbers), I'd like to know how to get COPY to do the right
thing.
I'd like to setup a table such as:
create table foo ( name text, id serial primary key);
select setval('foo_id_seq', 1);
copy foo from '...../foo.data';
where foo.data looks like this:
first line
second line
third line
and so on
When I try the above, the create and setval operations succeed, but the
copy operation warns me that:
ERROR: copy: line 1, CopyFrom: Fail to add null value in not null
attribute id
Obviously, my foo.data file doesn't contain enough columns, and copy
doesn't seem to want to generate default values for the SERIAL data type
'id' field. I suppose I could use the INSERT command, as in:
INSERT INTO foo (name) values('first line');
INSERT INTO foo (name) values('second line');
and so on, but that kinda defeats the purpose of the very convenient
COPY command, especially for very large amounts of data. So, I'm left
with writing a perl of python script to do essentially what I want COPY
to do in the first place: read my foo.data file and, for each line,
generate an INSERT statement and pass it along to psql.
Any help or suggestions?
