COPY INTO and the SERIAL data type
От | Jonathan Sand |
---|---|
Тема | COPY INTO and the SERIAL data type |
Дата | |
Msg-id | 20010510004728-r01010600-6e6e7614@10.1.1.3 обсуждение исходный текст |
Ответы |
Re: COPY INTO and the SERIAL data type
|
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: