Hello
Now perhaps this is a bit dumb, but...
I just populated a new table via \copy. After that, I realize that perhaps is a good thing to have a row identifier in it, so I try
clapidus=> alter table tickets add column rid serial;
NOTICE: ALTER TABLE will create implicit sequence "tickets_rid_seq" for SERIAL column "tickets.rid"
ERROR: adding columns with defaults is not implemented
So my next guess is to define a test table from scratch, this time with the serial field in place. Next I try the \copy:
clapidus=> create table test(rid serial, col_a text);
NOTICE: CREATE TABLE will create implicit sequence "test_rid_seq" for SERIAL column "test.rid"
CREATE TABLE
clapidus=> \d test
Table "test"
Attribute | Type | Modifier
-----------+---------+-------------------------------------------------------
rid | integer | not null default nextval('public.test_rid_seq'::text)
col_a | text |
clapidus=> \copy test from stdin
23 a record
45 another record
\.
clapidus=> select * from test ;
rid | col_a
-----+------------
23 | a record
45 | another record
(2 rows)
Now the first character from stdin is a tab, in a try to let the sequence come into action:
clapidus=> \copy test from stdin
still another record
\.
ERROR: invalid input syntax for integer: ""
PQendcopy: resetting connection
Grrr. Third attempt:
clapidus=> \copy test from stdin with null as 'NULL'
NULL still another one
\.
ERROR: null value for attribute "rid" violates NOT NULL constraint
PQendcopy: resetting connection
So? Is there a way to add the sequence to an existing table?
Or, alternatively, is there a way to issue a \copy command while letting the sequence fill in the serial field?
thanks in advance
cl.