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 SERIALIf 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:
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'));