Problems w. SERIAL

On Sat, 05 May 2001 19:34:06 +0200, Morten Primdahl <morten@primdahl.net> wrote:

Hi.

Have the following tables:

CREATE TABLE tbl_a (id SERIAL PRIMARY KEY, data VARCHAR(5));
CREATE TABLE tbl_b (id SERIAL PRIMARY KEY, data VARCHAR(5));

CREATE TABLE tbl_c
(id SERIAL PRIMARY KEY,
data VARCHAR(50),
a SERIAL CONSTRAINT a_ref REFERENCES tbl_a(id),
b SERIAL CONSTRAINT b_ref REFERENCES tbl_b(id)
);

The two inserts into tbl_a and tbl_b work fine:

INSERT INTO tbl_a (data) VALUES ('a data');
INSERT INTO tbl_b (data) VALUES ('b data');

But if I do

INSERT INTO tbl_c (data, a, b) VALUES ('c data',1,1);

I get ERROR: Relation 'tbl_c_id_seq' does not exist
even though that sequence was implicitly created upon
creation of tbl_c - or?

Can anyone help me out on this? Thanks.

Morten

Can you do

SELECT nextval( 'tbl_c_id_seq' );

in psql successfully?
if not, is the sequence actually there? try

\d

or

\d tbl_c_id_seq

if it is not there, you can create it yourself

CREATE SEQUENCE tbl_c_id_seq;

if it is there... not sure what to say.

\d tbl_c_id_seq

if it is not there, you can create it yourself

Thanks, the sequence was not there. Just puzzled me that
when creating tbl_c, I get:

test=# CREATE TABLE tbl_c
(id SERIAL PRIMARY KEY,
data VARCHAR(50),
a SERIAL CONSTRAINT a_ref REFERENCES tbl_a(id),
b SERIAL CONSTRAINT b_ref REFERENCES tbl_b(id)
);test-# test(# test(# test(# test(#
NOTICE: CREATE TABLE will create implicit sequence 'tbl_c_id_seq' for
SERIAL column 'tbl_c.id'
NOTICE: CREATE TABLE will create implicit sequence 'tbl_c_a_seq' for
SERIAL column 'tbl_c.a'
NOTICE: CREATE TABLE will create implicit sequence 'tbl_c_b_seq' for
SERIAL column 'tbl_c.b'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'tbl_c_pkey' for table 'tbl_c'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'tbl_c_a_key'
for table 'tbl_c'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'tbl_c_b_key'
for table 'tbl_c'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE

Thanks for the tip, I'll consider this a bug and
doublecheck the notices from postgres from now on.

Morten

Morten Primdahl <morten@primdahl.net> writes:

I get ERROR: Relation 'tbl_c_id_seq' does not exist
even though that sequence was implicitly created upon
creation of tbl_c - or?

Indeed, it should have been --- and is when I repeat the test
commands you give. Perhaps you mistakenly removed the sequence
later on?

regards, tom lane

Morten Primdahl <morten@primdahl.net> writes:

I get ERROR: Relation 'tbl_c_id_seq' does not exist
even though that sequence was implicitly created upon
creation of tbl_c - or?

Indeed, it should have been --- and is when I repeat the test
commands you give. Perhaps you mistakenly removed the sequence
later on?

I certain that I do not remove those. It happens every time
I run the aforementioned sequence. This is postgresql 7.0.3,
I'll upgrade to 7.1 and try again. Thanks.

Morten

On Sat, 5 May 2001, Morten Primdahl wrote:

CREATE TABLE tbl_a (id SERIAL PRIMARY KEY, data VARCHAR(5));
CREATE TABLE tbl_b (id SERIAL PRIMARY KEY, data VARCHAR(5));

CREATE TABLE tbl_c
(id SERIAL PRIMARY KEY,
data VARCHAR(50),
a SERIAL CONSTRAINT a_ref REFERENCES tbl_a(id),
b SERIAL CONSTRAINT b_ref REFERENCES tbl_b(id)
);

INSERT INTO tbl_c (data, a, b) VALUES ('c data',1,1);

I get ERROR: Relation 'tbl_c_id_seq' does not exist
even though that sequence was implicitly created upon
creation of tbl_c - or?

re: structure of table C, I think you're misunderstand something
about SERIALs. A SERIAL is just another name for an int that automatically
has a sequence set up for it. So, if in table C, you want to store
references to tables a and b, you want you schema to be:

CREATE TABLE tbl_c (
id serial primary key,
a int references tbl_a,
b int references tbl_b
);

note the use of INTs, not SERIALs.

I don't know if that will explain the weirdness of your error message,
but, even if not, it will certainly a future error! :-)

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Joel Burton wrote:

re: structure of table C, I think you're misunderstand something
about SERIALs. A SERIAL is just another name for an int that automatically
has a sequence set up for it. So, if in table C, you want to store
references to tables a and b, you want you schema to be:

[snip]

note the use of INTs, not SERIALs.

You're right, thanks! I assumed that SERIAL was a special data
type like eg. Oracle NUMBER. Using INT4 now as FK and it works like
a charm, thanks again :)

Morten

Morten Primdahl <morten@it-c.dk> writes:

I certain that I do not remove those. It happens every time
I run the aforementioned sequence. This is postgresql 7.0.3,
I'll upgrade to 7.1 and try again. Thanks.

Oh, I was trying 7.1. Now that I think about it, I think there's
a bug in 7.0.* that causes it to forget to create all the sequences
if you define more than one SERIAL column in a table. That might
be what you're seeing ...

regards, tom lane

On Sun, May 06, 2001 at 02:59:31AM +0200, Morten Primdahl wrote:

\d tbl_c_id_seq

if it is not there, you can create it yourself

Thanks, the sequence was not there. Just puzzled me that
when creating tbl_c, I get:

test=# CREATE TABLE tbl_c
(id SERIAL PRIMARY KEY,
data VARCHAR(50),
a SERIAL CONSTRAINT a_ref REFERENCES tbl_a(id),
b SERIAL CONSTRAINT b_ref REFERENCES tbl_b(id)
);

is there any paradigm wherein TWO serial values for one table
might possible be useful? (since serial is really "int default
nextval('sequence_seq')" how can the second serial be anything
but redundant?)

--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

On Wed, 9 May 2001, will trillich wrote:

On Sun, May 06, 2001 at 02:59:31AM +0200, Morten Primdahl wrote:

\d tbl_c_id_seq

if it is not there, you can create it yourself

Thanks, the sequence was not there. Just puzzled me that
when creating tbl_c, I get:

test=# CREATE TABLE tbl_c
(id SERIAL PRIMARY KEY,
data VARCHAR(50),
a SERIAL CONSTRAINT a_ref REFERENCES tbl_a(id),
b SERIAL CONSTRAINT b_ref REFERENCES tbl_b(id)
);

is there any paradigm wherein TWO serial values for one table
might possible be useful? (since serial is really "int default
nextval('sequence_seq')" how can the second serial be anything
but redundant?)

Well, given that you can set the sequence value yourself, and it doesn't
have to be unique, it *could* be useful.

Suppose you want to keep track of how many projects you did since you
started the company, and how many you did since you since you last
rebooted the server. You could have a SERIAL for the table that starts
counting at one, and just keeps counting, and have a different SERIAL that
you reset (via setval() every time you reboot.)

There are other ways of handling this, of course, count() and such, but in
some cases, this might be a decent solution to that problem, especially if
your client application is too dumb to use grouping queries or such.

In the case of the original question, I think he was confusing using a
serial-as-primary-key with using serial-as-foreign-key. Perhaps I lack
imagination, but I can't see any good reasons to use a serial as foreign
key.

(well, maybe I can. What if you had 100 prizes to give out to the first
hundred callers. you have a table with prizes, using a int SERIAL as the
primary key. in the callers table, you use something else as the primary
key--perhaps the callers phone number or somesuch. you have a serial
references to the table, so that you automatically link each new caller
with their prize. this also blocks entries after the first 100. i don't
think i'd do it this way, but it might be defensible.)

HTH,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

I see no reason why a second SERIAL column would be needed. As in the
example for creating tbl_c, when you have a SERIAL as a primary key,
the foreign keys that reference it should be int4 not SERIAL.
jeff

Jeff Daugherty
Database Systems Engineer
Great Bridge LLC

will trillich wrote: