I'm running postgresql 7.0.3 on redhat 7.0.
I want to use the COPY command to read a bunch of data files. These
files don't contain an id, so I want to use the SERIAL data type to
auto-number the generated rows. COPY complains.
The other recommended method (besides using SERIAL) is to use OID's.
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? Besides which, OID's are very
large, unfriendly and meaningless numbers.
Because of these (and possibly other?) drawbacks, I'd like to know how
to get COPY to do the right thing.
I'd like to setup and initialize a table in the following manner:
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 (contains no tabs or other delimiters):
first line
second line
third line
and so on
When I try the above, the create and setval operations succeed, but the
copy operation complains 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's not nearly as attractive as 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 think COPY
should 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? Is COPY simply not worked out to auto-number?
Is this a bug? Is this all working correctly in version 7.1?
PS, why do I have to call the setval function? Couldn't SERIAL use a
well-published default initial value when it creates foo_id_seq?