Skip site navigation (1) Skip section navigation (2)

Re: going crazy with serial type

From: Jason Earl <jason(dot)earl(at)simplot(dot)com>
To: Cindy <ctmoore(at)uci(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: going crazy with serial type
Date: 2002-01-31 20:41:57
Message-ID: 1012509718.24070.234.camel@npa01zz001 (view raw or flat)
Thread:
Lists: 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(at)uci(dot)edu
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



In response to

pgsql-general by date

Next:From: Ellen CyranDate: 2002-01-31 20:51:09
Subject: Re: Function to Pivot data
Previous:From: Tom LaneDate: 2002-01-31 20:41:03
Subject: Re: going crazy with serial type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group