PostgreSQL Autoincrement

postgresql auto-increment
Ian picture Ian · Apr 25, 2009 · Viewed 626.2k times · Source

I'm switching from MySQL to PostgreSQL and was wondering how I can do autoincrement values. I saw in the PostgreSQL docs a datatype "serial", but I get syntax errors when using it (in v8.0).

Answer

Trey picture Trey · Apr 25, 2009

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

SERIAL is just a create table time macro around sequences. You can not alter SERIAL onto an existing column.

Related questions

How to set auto increment primary key in PostgreSQL?

I have a table in PostgreSQL with many columns, and I want to add an auto increment primary key. I tried to create a column called id of type BIGSERIAL but pgadmin responded with an error: ERROR: sequence must have …

Read More
Reset auto increment counter in postgres

I would like to force the auto increment field of a table to some value, I tried with this: ALTER TABLE product AUTO_INCREMENT = 1453 AND ALTER SEQUENCE product RESTART WITH 1453; ERROR: relation "your_sequence_name" does not exist I'm new …

Read More
How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

Read More