adding SERIAL to a table

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.

Responses

Browse pgsql-general by date

  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!