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
- PostgreSQL 9.0 High Performance by Gregory Smith
- PostgreSQL 9 Administration Cookbook by S. Riggs and H. Krosing
- High Performance MySQL: Optimization, Backups, Replication, and More
Simon
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!
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.
Simon
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.
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
Simon
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.
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!
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.
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!
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!
Simon Harris
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.
jcwatson
I ran into the same problem and found this article very helpful. Thank you!
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! :)
JavaDude
CREATE TABLE mytable (
name VARCHAR(255),
my_id SERIAL,
primary key(my_id)
);
Is this hard?
Simon Harris
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.
eb
EXCELLENT! worked well! Thank you!
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
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
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
the_flexpert
Thanks for that fast MySQL code! Very entertaining discussion also. I vote to leave the topic title unchanged!
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!
Chris
Very helpful, thank you.
FreeArtMan
Good article.
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"
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.
Balu
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.
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?
Senthil Kumar
ya it is really useful for me.. thank a lot
Syed Ali
Thanks for the posts and comments. I found both very useful. cheers
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
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. ^^
Patrick
Thanks this was very usefull!
Tony T
Pretty useful, thanks so much.
Mukesh Kumar
Thanks This post is very very useful.
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?
Cycloneboy
Thanks! This worked for me.
Max Nunes
Thank you so much!!
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;
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?