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

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?

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!

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

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

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

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

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.

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;