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”);
May 21st, 2009 at
I’m not sure why you’re saying that PostgreSQL doesn’t have an “auto increment” type, when it clearly does, and you just talked about it: serial and bigserial (aka serial8). If by type, you mean you don’t do this:
column_name int autoincrement
Then, no, I guess it’s not the same, but the functionality is identical.
May 21st, 2009 at
Sorry if you misunderstood. When I was creating my table I was expecting a data type named “auto increment”. I had to google what the type name was for postgres so it’s not really that straight forward.