Re: going crazy with serial type

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
>