5 Mar 2008, 1:01 a.m.

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

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

Posted by Simon at 01:53:00 PM
8 May 2008, 5:46 p.m.

Paul F

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?

24 May 2008, 5:14 p.m.

Simon [ADMIN]

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!

25 Jul 2008, 4:21 p.m.

Andy Chapman

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.

27 Jul 2008, 2:20 a.m.

Simon [ADMIN]

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.

31 Jul 2008, 9:19 a.m.

James

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

1 Aug 2008, 1:37 a.m.

Simon [ADMIN]

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.

6 Aug 2008, 8:52 a.m.

Matthew Miller

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!

30 Oct 2008, 9:06 p.m.

Marko

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.

10 Nov 2008, 10:49 p.m.

JP

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!

15 Dec 2008, 6:55 a.m.

tanoshimi

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!

17 Dec 2008, 2:46 p.m.

Simon [ADMIN]

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.

2 Jan 2009, 10 a.m.

jcwatson

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

23 Feb 2009, 10:23 p.m.

JP

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! :)

25 Feb 2009, 4:35 p.m.

JavaDude

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

Is this hard?

1 Mar 2009, 12:52 a.m.

Simon [ADMIN]

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.

20 Mar 2009, 12:50 a.m.

eb

EXCELLENT! worked well! Thank you!

7 Apr 2009, 9:08 a.m.

Israel

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

29 Apr 2009, 5:54 a.m.

nugros

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

thanks

23 May 2009, 6:14 p.m.

Stan

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

26 May 2009, 8:10 p.m.

the_flexpert

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

23 Jun 2009, 10:44 p.m.

Ryan

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!

24 Jul 2009, 11:27 p.m.

Chris

Very helpful, thank you.

16 Aug 2009, 10:44 p.m.

FreeArtMan

Good article.

10 Oct 2009, 10:12 a.m.

WorldWalker

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"

18 Feb 2010, 5:59 a.m.

jae

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.

24 Jun 2010, 10:50 p.m.

Zach

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?

27 Sep 2010, 7:51 p.m.

Senthil Kumar

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

5 Dec 2010, 8:38 p.m.

Syed Ali

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

20 Feb 2011, 4:17 p.m.

Random

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

23 Mar 2011, 4:51 p.m.

xfce

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. ^^

23 Apr 2011, 11:45 a.m.

Patrick

Thanks this was very usefull!

13 May 2011, 11:01 p.m.

Tony T

Pretty useful, thanks so much.

10 Jun 2011, 11:08 a.m.

Mukesh Kumar

Thanks This post is very very useful.

8 Jul 2011, 8:32 a.m.

Yasiru

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?

10 Nov 2011, 6:24 a.m.

Cycloneboy

Thanks! This worked for me.

12 Jul 2012, 4:18 a.m.

Max Nunes

Thank you so much!!

9 Nov 2012, 5:06 p.m.

Sunil K Mathew


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

ALTER TABLE your_table
ADD colomn_name serial UNIQUE;