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),
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”);

Technorati Profile


Feb 17 2009

Zmanda launches third version of Amanda Enterprise – eChannelLine

Zmanda launches third version of Amanda Enterprise
eChannelLine – 31 minutes ago
By Chris Talbot Zmanda is continuing its push to convert people to open source for data protection with the launch of Amanda Enterprise 3.0.
Podcast: Zmanda offers cloud backup for small businesses CNET News
Zmanda’s 3.0 backup supports cloud, Sharepoint, PostgreSQL ZDNet
all 3 news articles