CREATE SEQUENCE test_seq
CREATE TABLE test
(
test_id INTEGER DEFAULT NEXTVAL('test_seq'),
test_name VARCHAR(64),
CONSTRAINT test_pk PRIMARY KEY(test_id)
);
INSERT INTO test (test_name) VALUES ('Test Name');
INSERT INTO test (test_name) VALUES ('Test Name2');
This is what you are looking for i believe
The sequence number
Note i believe SERIAL just creates a sequence with the table name _seq and
then defaults that field to that value
Darren Ferguson
On Thu, 31 Jan 2002, Cindy wrote:
>
> OK. My background is in mysql, and I've been converting over to psql. Just
> by way of background.
>
> I do !NOT! understand how the SERIAL type works. I want something
> like I had in mysql that would generate new, unique numbers, each time
> I added a new record. I want something that sits down, shuts up, and
> just GIVES me the number on demand. (I also want a guarantee that the
> unique number is consecutive, and is never zero or negative.) In short,
> I want the AUTO_INCREMENT behavior.
>
> But so far it's been one giant headache. Tell me, how do I insert
> new records into a table *without* specifying an actual number? In
> mysql it's just an empty field. I have been unable to determine how
> to do this in psql other than to ascertain it certainly isn't through
> the same way.
>
> I've been through the documentation, but for some reason, no one seems
> to think a programmer would ever want functionality like mysql's
> AUTO_INCREMENT, so as far as I can tell, it's not even addressed.
>
> I'd appreciate any help. I basically have a table:
>
> create table mytable (mytable_id serial, a int, b int);
>
> and
>
> insert into mytable ('', 1, 2); is accepted but then following
> insert into mytable ('', 5, 6); etc, is rejected due to "duplicate key"
>
>
> Thanks,
> --Cindy
> --
> ctmoore@uci.edu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
