From: | "Claudio Lapidus" <clapidus(at)hotmail(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | adding SERIAL to a table |
Date: | 2003-09-02 02:08:05 |
Message-ID: | BAY7-DAV8QseRcBmDck0000468b@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Vivek Khera | 2003-09-02 02:08:55 | Re: Commercial postgresql |
Previous Message | 3rd. | 2003-09-02 00:57:43 | YOUR SITES SEARCH FEATURE DOES NOT WORK! |