Re: Equivalent for AUTOINCREMENT?

On Fri, Oct 31, 2008 at 03:30:44AM +0100, Michelle Konzack wrote:
> I have a table where I have a serialnumber which shuld be increased be
> each INSERT. I know I can use max() to get the highest number, but how
> can I use it in a INSERT statement?

Just don't mention the column. For example, say I had the following table:

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);

And I wanted to insert a new user, I'd do:

INSERT INTO users (name) VALUES ('Sam Mason');

If I wanted to refer back to the identifier I'd just used, there are a
couple of options depending on what you want to do. If it's just your
code that wants to know, use the RETURNING clause:

INSERT INTO users (name) VALUES ('Sam Mason')
RETURNING (id);

If you want to use the value inside more SQL code and don't want more
round trips between your code and the database you can do:

BEGIN;
INSERT INTO users (name) VALUES ('Sam Mason');
INSERT INTO sessions (userid, startdate)
VALUES (currval('users_id_seq'), now());
SELECT currval('users_id_seq');
COMMIT;

The "users_id_seq" here identifies the sequence that was created when
the table was created; if you're not sure what this is you can use the
pg_get_serial_sequence() function[1]. The SELECT statement at the end
will cause the database to return the generated code back to you so
it can be used elsewhere. The reason for having to name the sequence
explicitly is that because of triggers and other complications multiple
sequences can get involved, when you'd only expect one. Because of
this if there was just a simple function like MySQL's LAST_INSERT_ID()
Postgres wouldn't know which sequence you were actually wanted to refer
to.

Sam

[1] http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE