November 20, 2004

PostgreSQL | New in 8.0: Extra SERIAL functionality

Among the many "headline" features scheduled for the upcoming PostgreSQL 8.0 release, such as the Windows version, there is a whole bunch of less glamourous additions which might not blow the PHB's mind, but make life that little bit easier for the humble DBA / developer. Some of these involve PostgreSQL's SERIAL type.

  • Firstly, it is now possible to add SERIAL columns using ALTER TABLE:
    ALTER TABLE whatever ADD newcol SERIAL
    If the table contains rows, all columns will be populated with a sequential value, although in no particular order.
  • Names of sequences automatically generated by a SERIAL column definition are always constructed as tablename_column_name_seq to ensure uniqueness within a schema.
  • A new function, pg_get_serial_sequence() returns a SERIAL column's sequence name if provided with the name of a table and a SERIAL column contained in that table. This can then be fed for example to curval(), to ascertain the value of a newly inserted primary key column:
    SELECT pg_get_serial_sequence('whatever','newcol')

To recap:

CREATE TABLE serial_exmpl (
  val TEXT
INSERT INTO serial_exmpl VALUES('Hello');
ALTER TABLE serial_exmpl ADD id SERIAL;

INSERT INTO serial_exmpl VALUES('World');
SELECT currval(pg_get_serial_sequence('serial_exmpl','id'));
Posted at 10:11 PM