[GENERAL] adding SERIAL to a table

[Claudio Lapidus]

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.