May
19
2009
Auto Increment in Postgres like Mysql
Why isn’t there a ‘auto increment’ type in postgres?
When I was creating a table in postgres earlier today I needed a unique identifier to use as a foreign key in other tables. There were no unique fields in the postgres table so I wanted to create an auto increment column type. I was surprised after browsing the field types in pgAdmin III that there is no ‘auto increment’ data type in postgres. I was expecting one because I’m so used to using mysql for the actual creation of databases. It seems that when using postgres we are expected to use either sequences for the very specifics or the serial data type for the basic +1 auto increment integer.
Here’s an example of using sequences :
First create the sequence:
CREATE SEQUENCE codys_sequence;
Then create the postgres table:
CREATE TABLE some_postgres_table (
id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval(‘codys_sequence”),
some_field VARCHAR(50),
id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval(‘codys_sequence”),
some_field VARCHAR(50),
some_other_field VARCHAR(100)
);
It is a lot easier to just let the postgres database handle the creation and automation by specifying the field as a SERIAL type. Sequences are mostly useful when you want to increment the integer by a certain value like if you’re using master-master replication and don’t want to worry about race conditions.
With either a sequence or a serial field type it is much easier to insert data than with manually updated integer because you don’t need to include anything about the field in your insert statements.
INSERT into some_postgres_table (some_field,some_other_field) values (“auto”,”increment”);
