MySQL versus PostgreSQL: Adding an Auto-Increment Column to a Table
Posted in Databases and Programming on Wednesday, the 5th of March, 2008.
The bulk of my database experience (almost eight years now) has been with the popular open-source MySQL database management system. MySQL has progressed significantly over the years, and has grown into a remarkable product. It finally has all the must-have features such as views, stored procs and referential integrity, coupled with the blistering performance for which MySQL has always been known. In short, it rocks.
But I digress. I've recently been having to get to grips with PostgreSQL (an old version of course - 7.1 or so - just to make life really interesting). It's largely intuitive, but there are quirks around most corners. This is my favourite so far: I recently needed to add an auto-incrementing integer "id" column to a table.
MySQL
This sort of thing will be second nature to MySQL developers:
ALTER TABLE mytable
ADD myid INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;
One SQL command - not bad.
PostgreSQL
It turned out not to be so easy with our Postgres installation. For a start, there are no auto_increment columns. So, as with several other major RDBMSs, the solution is to create a "sequence", which is kind of like a pseudo-table which acts as a counter:
CREATE SEQUENCE mytable_myid_seq;
Next, we have to add our new column to the table, and specify that for each new row it should take its value from the sequence, using the NEXTVAL() function. For reasons best known to the Postgres guys, you can't do this in one step:
ALTER TABLE mytable
ADD myid INT UNIQUE;
And then:
ALTER TABLE mytable
ALTER COLUMN myid
SET DEFAULT NEXTVAL('mytable_myid_seq');
We're getting there. We now have an auto increment column. The problem is that Postgres won't backfill this with values automatically: all pre-existing rows are currently null for this column. Let's change that:
UPDATE mytable
SET
myid = NEXTVAL('mytable_myid_seq');
Job done. Well, some time later, the job will be done. That final step is one of the slowest things you can possibly ask Postgres to do. For a mid-sized table (around 5,000,000 rows, with a handful of small numeric and text columns), that took about 2.5 hours on powerful hardware - so you'll want to leave this for a quiet time. Fortunately Postgres treats the UPDATE as an atomic transaction: nothing is committed until the command completes, so it will be difficult for you to leave the data in an inconsistent state.
Posted by Paul F on Thursday, the 8th of May, 2008.
Postgres auto-increment column in 1 step:
alter table mytable add myid serial;
Type 'serial':
1. Sets type to int.
2. Creates sequence for you.
3. Populates unique values in all existing rows using sequence.
4. sets default on column to nextval of sequence.
Still 1-0 to mysql?