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

Re: Equivalent for AUTOINCREMENT?

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Equivalent for AUTOINCREMENT?
Date: 2008-10-31 14:12:38
Message-ID: 20081031141238.GH2459@frubble.xen.chris-lamb.co.uk (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Grzegorz JaśkiewiczDate: 2008-10-31 14:28:33
Subject: Re: Equivalent for AUTOINCREMENT?
Previous:From: Ivan Sergio BorgonovoDate: 2008-10-31 14:12:06
Subject: Re: Are there plans to add data compression feature to postgresql?

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