Re: going crazy with serial type - Mailing list pgsql-general

Cindy,

You are quite close to the solution.  You already have the correct
datatype, now all you need is the correct syntax.

Here's an example:

CREATE TABLE foo (
    bar        SERIAL PRIMARY KEY,
    a        int,
    b        int,
);

Now insert into this table like this:

INSERT INTO foo (a, b) VALUES (1, 2);
INSERT INTO foo (a, b) VALUES (3, 4);

To verify that it works do this:

processdata=> SELECT * FROM foo;
 bar | a | b
-----+---+---
   1 | 1 | 2
   2 | 3 | 4
(2 rows)

Notice that I didn't specify a value for the bar column when I inserted
my values, this causes PostgreSQL to choose the default value (which in
this case is the nextval() from the sequence created by the serial
type).

Now, integers provided in this fashion are guaranteed to be unique each
time, but they aren't guaranteed to be consecutive.  PostgreSQL doesn't
try and reuse sequence numbers on failure so if you have a lot of failed
inserts you *will* see holes in your sequences.  This means that the
numbers might be (1, 2, 4, 5, 7) or something instead of (1, 2, 3, 4,
5).  Personally, I think that it is an acceptable price to pay for a
database where inserts don't lock up the entire table, but if your
numbers have to be consecutive it is something to think about.

Hope this was helpful,
Jason

On Thu, 2002-01-31 at 12:40, 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