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.

Related Reading

Comments

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?

Posted by Simon Harris on Saturday, the 24th of May, 2008.

Hi Paul - thanks for stopping by.

That's certainly true in the latest Postgres releases, but is in fact a relatively new feature.

Unfortunately, for the time being, I'm lumbered with 7.something, where that SQL fails with the infamous "ERROR: adding columns with defaults is not implemented" message.

Hence the long-winded workaround!

Posted by Andy Chapman on Friday, the 25th of July, 2008.

You might also say that MySQL 5 is relatively new too. So if we're comparing old databases together, MySQL 4 was not in the same ball park as Postgres 7.

Posted by Simon Harris on Sunday, the 27th of July, 2008.

Hi Andy, thanks for your comment. It's really nice to see how many Postgres guys have stumbled upon these posts :)

MySQL5 isn't especially new, but that's hardly the point: MySQL has had auto increment columns since...well, longer than anyone cares to remember.

My post isn't about which is the better RDBMS, it's merely about my own experiences getting rudimentary functionality out of a version of Postgres which, to be fair, is still widely in production use.

Posted by James on Thursday, the 31st of July, 2008.

Apparently the SERIAL type was added in version 6.4 back in 1998. You sure it doesn't work in version 7.1?
http://www.postgresql.org/docs/6.4/static/release.htm#AEN12985

Posted by Simon Harris on Friday, the 1st of August, 2008.

I'm afraid so. The existence of the SERIAL type is not the issue, it's the ability to add it to an existing table (see the title of the post).

Apologies if the title, post and comments don't make it clear. I would reword things but I'm failing to see the ambiguity right now.

The longer answer is that adding a SERIAL to a table is merely a shorthand for adding a column and setting a default for it in one step, which is not allowed until much later versions of Postgres.

Posted by Matthew Miller on Wednesday, the 6th of August, 2008.

I developed a system that used on Postgres 7.x almost three years ago and was able to create my auto incrementing id using SERIAL. The problem, as you pointed out, was on an existing table. How the table came to existence without an auto incrementing field is not made clear, but surely that should have been added when table was created, right? I like MySQL, especially with the progress it has made (as you noted), but I don't see it having an upper hand in this case because: 1) you're using a well outdated version of PostgreSQL, and 2) the table in question was not created with the auto incrementing field from the start. I was able to add a SERIAL field with to a table that had none and have backfill; however, this was on 8.2.5 so this particular argument doesn't carry much weight because of the version mismatch.

On a side note, why must this textarea for comments be so tiny!? Ciao!

Posted by Marko on Thursday, the 30th of October, 2008.

You are comparing two products on a single attribute (from more than thousand attributes). So before making final conclusions a fair comparison should be made.

Posted by JP on Monday, the 10th of November, 2008.

PG, the only conclusion I could find here is that its more work to add an auto-increment column to a pg db < v7.3 than to a mysql db.
Though I would have liked it if the comparison was based on the current version of both dbs and their structural/implementation differences for auto-incrementing columns were compared. Then I think the title would be more apt. IMHO.

cheers!

Posted by tanoshimi on Monday, the 15th of December, 2008.

As a random passer-by who was just strolling past this page on the internet, it certainly seems to me that there are a lot of defensive postgres fans out there!
I read this article as a comment on one particular feature (adding an auto-increment to an *existing* table) using one of two named versions of popular DBs... and I can confirm that the result is exactly as the original article states.

Where's all this stuff about making a fair comparison from? Simply saying 'You should upgrade to Postres 2009' is not a solution to the problem. And it's such a Microsoft answer!

Anyway - thanks for the post - I found it helpful anyway!

Posted by Simon Harris on Wednesday, the 17th of December, 2008.

JP - That's the only conclusion I would expect you to find: that's the point of the post :)

tanoshimi - Thanks! Glad to find someone who reads the post before commenting.

Posted by jcwatson on Friday, the 2nd of January, 2009.

I ran into the same problem and found this article very helpful. Thank you!

Posted by JP on Monday, the 23rd of February, 2009.

Simon,

I was just clearing that up for the other commentators. PG guys seem to be too defensive, since the post title seem to compare the two DBs features, as they are at the present.

If you changed the title to:

"MySQL versus PostgreSQL 7.1: Adding an Auto-Increment Column to a Table"

That should clear up some of the confusion. PG 8.1 already auto fills values when you add a serial column to a table. Haven't tested this on < 8.1.

peace!

ps. this post is almost a year old! :)

Posted by JavaDude on Wednesday, the 25th of February, 2009.

CREATE TABLE mytable (
name VARCHAR(255),
my_id SERIAL,
primary key(my_id)
);

Is this hard?

Posted by Simon Harris on Sunday, the 1st of March, 2009.

JP - Thanks, and point taken. Incidentally, I've merged your two later comments, let me know if I've misrepresented you at all.

JavaDude - clearly it's not as hard as reading a weblog post before commenting, but thanks for playing.

Posted by eb on Friday, the 20th of March, 2009.

EXCELLENT! worked well! Thank you!

Posted by Israel on Tuesday, the 7th of April, 2009.

I think the point postgres adressed way earlier than mysql was tought that way to offer flexibility.

Imagine a scenario where you'd want some column for two different tables auto-generated and that you wanted no overlapping of values for those two. Of course this is a kind of a odd scenario yet i think its better to provide a way "out" for the most cases possible. After that was done implementing a auto_increment under the hood with serial type was probably a piece of cake.

In the other hand if you wanted to do something like this on mysql you'd probably stumble on this(http://forums.mysql.com/read.php?61,143867,143867#msg-143867).

Both RDBMS have simply two different stories and development roads.

I should add that accessibility mysql focused way before postgres is the reason why it's adopted by the masses.

Note: This is not a religious comment. My beliefs are intact after reading this post. :P

Posted by nugros on Wednesday, the 29th of April, 2009.

when i change UNIQUE become PRIMARY KEY ,at column myid not appear number of autoincrement.....
why happened it???
I want myid become PK ...

thanks

Posted by Stan on Saturday, the 23rd of May, 2009.

Your post has useful information but the title is definitely misleading because it doesn't specify the version. PostgreSQL 7.1 was released in 2001 and your blog entry is dated 2008, by which time there were 114 new releases! The current version at the time was 8.3.

(1) http://www.postgresql.org/docs/current/static/release.html

Posted by the_flexpert on Tuesday, the 26th of May, 2009.

Thanks for that fast MySQL code! Very entertaining discussion also. I vote to leave the topic title unchanged!

Posted by Ryan on Tuesday, the 23rd of June, 2009.

Coming in late here, but I'm helping a buddy with some PHP forum software and he's running a PostgreSQL back end (which I'm not familiar with at all). I found this tip to be very helpful as I was searching specifically for adding an auto incrementing primary key to an existing table.

The reason the PostgreSQL guys are all on the defense is due to the commentary put in the article. "This takes so long in Postgre" and "Look how quickly it was done in my SQL". So that got their panties in a bunch haha. Probably should have stayed neutral and called the article "How to add an autoincrement primary key in both mySQL and Postgre". Step 1 - "x". No bias/commentary. But then again, who cares?

Cheers!

Posted by Chris on Friday, the 24th of July, 2009.

Very helpful, thank you.

Posted by FreeArtMan on Sunday, the 16th of August, 2009.

Good article.

Posted by WorldWalker on Saturday, the 10th of October, 2009.

I wonder why are we talking about the fans out there! It's not a question of fans truly but it's the other side or vision, I hope we could respect both sides anyway as it's out here!

"Postgres Lover"

Posted by jae on Thursday, the 18th of February, 2010.

wow, i'm just starting to use postgre and came across this. found it funny how some simple(possibly unintentional) comments about postgresql can get the postgresql users all defensive and turn this into a mysql vs postgres post.

Posted by Balu on Saturday, the 1st of May, 2010.

MySQL is much more simpler. It's simplicity makes it so famous. But I like the technology behind PostgreSQL much more. I have never tried your scenario (adding an autoincrement field to an **existing** table), because I have always worked with well designed DB-s. (Bless God for it!)

The post was good, the comments were awful.

Posted by Zach on Thursday, the 24th of June, 2010.

Just came across this when I realized I created three tables without auto-incrementing id fields (Postgres). I'm not normally our table implementer, and if they weren't for my own internal use I'd have asked someone in data to pop 'em in for me.

Your post was perfect, and on the topic of MySQL (which I was more familiar with) and Postgres (what I work with now and, personally, like more), I would assume that creating a sequence instead of just saying "hey, auto-increment this column for me" means that multiple columns across different tables can share a sequence, no?

Posted by Senthil Kumar on Monday, the 27th of September, 2010.

ya it is really useful for me.. thank a lot

Posted by Syed Ali on Sunday, the 5th of December, 2010.

Thanks for the posts and comments. I found both very useful. cheers

Posted by Random on Sunday, the 20th of February, 2011.

Yes, SERIAL is the equivalent function.

CREATE TABLE foo (
id SERIAL,
bar varchar);

INSERT INTO "foo" (bar) values ('blah');
INSERT INTO "foo" (bar) values ('blah');

SELECT * FROM foo;

1,blah
2,blah

Posted by xfce on Wednesday, the 23rd of March, 2011.

Thanks for the article, it helped me with my old version of postgresql.

I also liked the whining of the pg-guys in the comments a lot. ^^

Posted by Patrick on Saturday, the 23rd of April, 2011.

Thanks this was very usefull!

Posted by Tony T on Friday, the 13th of May, 2011.

Pretty useful, thanks so much.

Posted by Mukesh Kumar on Friday, the 10th of June, 2011.

Thanks This post is very very useful.

Posted by Yasiru on Friday, the 8th of July, 2011.

Is anyone know how to create empty table copy of existing table. I found that following sql will create a copy but with same sequence table.
CREATE TABLE tablename (LIKE oldtablename INCLUDING DEFAULTS)

But I want a new empty table copy with new sequence.
Any idea?

Posted by Cycloneboy on Thursday, the 10th of November, 2011.

Thanks! This worked for me.

Posted by Max Nunes on Thursday, the 12th of July, 2012.

Thank you so much!!

Posted by Sunil K Mathew on Friday, the 9th of November, 2012.


Try the following query to create an auto increment colomn on exixting table

ALTER TABLE your_table
ADD colomn_name serial UNIQUE;

Enter your comment: