By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

adding SERIAL to a table

P: n/a
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 theserial 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.

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Do it in three steps...

CREATE SEQUENCE tickets_rid_seq;
ALTER TABLE tickets ADD COLUMN rid INTEGER;
ALTER TABLE tickets ALTER COLUMN rid SET DEFAULT nextval('tickets_rid_seq')


""Claudio Lapidus"" <cl******@hotmail.com> wrote in message
news:BA*************************@hotmail.com...
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.
Nov 11 '05 #2

P: n/a
"Claudio Lapidus" <cl******@hotmail.com> writes:
So? Is there a way to add the sequence to an existing table?
Sure. You have to break the SERIAL down to its component parts though.
Something like

CREATE SEQUENCE seq;
ALTER TABLE tab ADD COLUMN ser INTEGER;
UPDATE tab SET ser = nextval('seq'); -- this will take awhile
ALTER TABLE tab ALTER COLUMN ser SET DEFAULT nextval('seq');
ALTER TABLE tab ALTER COLUMN ser SET NOT NULL;
-- possibly also add a UNIQUE constraint

We haven't yet got round to supporting ADD COLUMN ... DEFAULT because
according to the SQL spec that implies doing the UPDATE to fill the
column values immediately, and that's just a chunk of code no one's
written yet.
Or, alternatively, is there a way to issue a \copy command while letting th=
e sequence fill in the serial field?


Yes, you have to list in the COPY command just the columns that are
actually being supplied in the input data. Columns not mentioned (like
the serial column) get filled from their defaults. I think this is
new in 7.3 ... it's pretty recent anyway.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #3

P: n/a
On Mon, 1 Sep 2003, Tom Lane wrote:
"Claudio Lapidus" <cl******@hotmail.com> writes:
So? Is there a way to add the sequence to an existing table?


Sure. You have to break the SERIAL down to its component parts though.
Something like

CREATE SEQUENCE seq;
ALTER TABLE tab ADD COLUMN ser INTEGER;
UPDATE tab SET ser = nextval('seq'); -- this will take awhile
ALTER TABLE tab ALTER COLUMN ser SET DEFAULT nextval('seq');
ALTER TABLE tab ALTER COLUMN ser SET NOT NULL;
-- possibly also add a UNIQUE constraint


For folks just starting out, you can also do it this way:

=>begin;
=>create table a (info text, date date);
CREATE TABLE
=> insert into a values ('abc','2003-04-03');
INSERT 1127459 1
=> create table b (info text, date date, id serial);
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for SERIAL
column "b.id"
CREATE TABLE
=> insert into b (select * from a);
INSERT 1127468 1
=> select * from b;
info | date | id
------+------------+----
abc | 2003-04-03 | 1

=>drop table a;
DROP TABLE
=> alter table b rename to a;
ALTER TABLE
=>commit;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.