What's the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?

Question

I'm switching from MySQL to PostgreSQL and I was wondering how can I have an INT column with AUTO INCREMENT. I saw in the PostgreSQL docs a datatype called SERIAL, but I get syntax errors when using it.

Trixi

if you'd provide the query and error you're getting - perhaps someone could tell you what is wrong with the query. – Kalila

My first hit too Mich' and as it's a question that gets enough views to be relevant, why not vote it up. PS it's not trivial if you don't know how to do it. – Stagger

SERIAL is the preferred choice if your client driver is Npgsql. The provider is internally selecting new values after an INSERT using SELECT currval(pg_get_serial_sequence('table', 'column')). This will fail if the underlying column is not of type serial (numeric type + explicit sequence for instance) – Bartley

Just for curiousity... Why does someone have to migrate from MySQL which is very good, to PostgreSql? – Avens

...which is even better. – Makedamakefast

Answer 1

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.

Earleenearlene

Since which version could this datatype be found? I've been using sequences.nextval in pgsql 'cause I haven't noticed such a thing. Thanks. – Madness

as he said, it is just a macro around sequences. the SERIAL type is just an integer, and a sequence, with the column default to the sequences next value. Unlike MySQL there is nothing special and voodooey that goes on. – Moulding

because unless you have a table "Table" and "table" then just leave it unquoted and canonicalize it to table. The convention is simply never to use quotes in Pg. You can, if you want, use mixed case names for appearance, just don't require it: CREATE TABLE fooBar ( .. ); SELECT * FROM fooBar; will work, as will SELECT * FROM foobar. – Moulding

plus...the standard for most relational db implementations is to not have quotes. It makes it simpler to swap database implementations if ever necessary. – Exult

It is a bad idea to use upper case characters for identifiers in postgresql to begin with. If you refrain from such folly, you'll never have to quote an identifier. – Christi

@EvanCarroll try INSERT INTO user without quotes, please. – Curcuma

Per postgres doc, either consistently quote or unquote: postgresql.org/docs/current/interactive/… – Hephaestus

Evan Carroll: If you have a table "Table" and a table "table", I would suspect that you have some problematic masochistic tendencies, and I'd recommend that you speak to a psychiatrist. – Uniliteral

There is nothing wrong with quoting. In fact, it's Postgres that's the weird one here with a caseless convention for schema. Migrating from any other database requires this. – Stateless

"Per postgres doc, either consistently quote or unquote" But what if you have to quote to escape a reserved word one day you are left with a choice. To either make en exception to the rule just this once (which is NOT full consistency) or retroactively changes all other sql to use quotes (big waste of time). Why not start with quotes from the get go? Works in ever situation where as not quoting does not. Be consistent with the method that will work in ALL cases. OR just not care about perfect consistency. What advantage is there to being consistent here? None. Move on to bigger problems. – Trophoplasm

While this answer has been the right one for many years, since Postgres 10 you can use IDENTITY instead, as described by @a_horse_with_no_name at https://mcmap.net/q/63542/-what-39-s-the-postgresql-datatype-equivalent-to-mysql-auto-increment – Jeanette

quoting the column and names of tables is GOOD practice. For example CREATE TABLE `table` (`id` SERIAL, `bar` varchar). Without it the SQL is not correct (table is reserved word). But... Please note of type of quoting. Not double quote but single reverse quote. – Conventionality

Not really, this answer should not be the accepted one, read the answers by @Programster. – Unoccupied

and @a_horse_with_no_name – Unoccupied

Answer 2

You can use any other integer data type, such as smallint.

Example :

CREATE SEQUENCE user_id_seq;
CREATE TABLE user (
    user_id smallint NOT NULL DEFAULT nextval('user_id_seq')
);
ALTER SEQUENCE user_id_seq OWNED BY user.user_id;

Better to use your own data type, rather than user serial data type.

Tronna

I'd say this is actually the better answer because it allowed me to modify a table I had just created in PostgreSQL by setting the columns default (after reading up on CREATE SEQUENCE postgresql.org/docs/8.1/interactive/sql-createsequence.html ). HOWEVER, I'm not quite sure why you changed the owner. – Barrator

@JayC: From documentation: Lastly, the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped. – Outplay

why doesn't postgres community just reinvent the autoincrement keyword? – Hock

@Dr Deo : they use serial instead autoincrement keyword, i don't know why :) – Tronna

There's also smallserial if you just want a smaller data type. – Amblyopia

There's also bigserial if you want a bigger data type that will never run out of unique values. – Kelliekellina

As a MySQL fan I can't imaging the life without UNSIGNED and AUTO_INCREMENT, those give u flexibility to choose how to define any column and for sure how to ALTER the latter. I'm also do not like the CREATE SEQUENCE approach neither it's ability to increment notwithstanding on failures of an INSERT query. – Logan

Why is it better to use your own data type? In the current implementation, using SERIAL is the same as doing what you showed using type integer: postgresql.org/docs/9.1/datatype-numeric.html#DATATYPE-SERIAL – Shelah

Should user_id in the example be a PRIMARY KEY? I think so. – Zilla

i.e. user_id smallint NOT NULL PRIMARY KEY DEFAULT nextval('user_id_seq') – Zilla

I never understood why some developers reinvent the wheel or complicate things. If Postgres already has an internal mechanism optimized and created specifically for this problem (Serial), why make everything more complicated by creating a sequence? – Monometallic

Answer 3

If you want to add sequence to id in the table which already exist you can use:

CREATE SEQUENCE user_id_seq;
ALTER TABLE user ALTER user_id SET DEFAULT NEXTVAL('user_id_seq');

Tammeratammi

What is sequence? Where is AUTO_INCREMENT? – Agata

@Green: AUTO_INCREMENT isn't part of the SQL standard, it's specific to MySQL. Sequences are something that do a similar job in PostgreSQL. – Amblyopia

if you use 'id SERIAL', it will automatically creates a sequence in PostgreSQL. Name of that sequence will be <table name>_<column name>_seq – Gloucester

Don't you have to use ALTER COLUMN user_id? – Jaborandi

I tried this method but I get an error: ERROR: syntax error at or near "DEFAULT" Any suggestions? – Flare

Please follow this answer. This is a complete solution: drupal.stackexchange.com/a/175723/2153 – Trifoliate

Answer 4

Starting with Postgres 10, identity columns as defined by the SQL standard are also supported:

create table foo 
(
  id integer generated always as identity
);

creates an identity column that can't be overridden unless explicitly asked for. The following insert will fail with a column defined as generated always:

insert into foo (id) 
values (1);

This can however be overruled:

insert into foo (id) overriding system value 
values (1);

When using the option generated by default this is essentially the same behaviour as the existing serial implementation:

create table foo 
(
  id integer generated by default as identity
);

When a value is supplied manually, the underlying sequence needs to be adjusted manually as well - the same as with a serial column.

---

An identity column is not a primary key by default (just like a serial column). If it should be one, a primary key constraint needs to be defined manually.

Feral

generated always as identity is from SQL 2003 standard. You can find it in H2 & Oracle. Thx God I'm in 2021 )) – Heda

what's the benefit to using this over SERIAL? – Staphylorrhaphy

@Staphylorrhaphy wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial – Feral

2ndquadrant.com/en/blog/postgresql-10-identity-columns – Breakaway

Answer 5

Whilst it looks like sequences are the equivalent to MySQL auto_increment, there are some subtle but important differences:

1. Failed Queries Increment The Sequence/Serial

The serial column gets incremented on failed queries. This leads to fragmentation from failed queries, not just row deletions. For example, run the following queries on your PostgreSQL database:

CREATE TABLE table1 (
  uid serial NOT NULL PRIMARY KEY,
  col_b integer NOT NULL,
  CHECK (col_b>=0)
);

INSERT INTO table1 (col_b) VALUES(1);
INSERT INTO table1 (col_b) VALUES(-1);
INSERT INTO table1 (col_b) VALUES(2);

SELECT * FROM table1;

You should get the following output:

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
(2 rows)

Notice how uid goes from 1 to 3 instead of 1 to 2.

This still occurs if you were to manually create your own sequence with:

CREATE SEQUENCE table1_seq;
CREATE TABLE table1 (
    col_a smallint NOT NULL DEFAULT nextval('table1_seq'),
    col_b integer NOT NULL,
    CHECK (col_b>=0)
);
ALTER SEQUENCE table1_seq OWNED BY table1.col_a;

If you wish to test how MySQL is different, run the following on a MySQL database:

CREATE TABLE table1 (
  uid int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  col_b int unsigned NOT NULL
);

INSERT INTO table1 (col_b) VALUES(1);
INSERT INTO table1 (col_b) VALUES(-1);
INSERT INTO table1 (col_b) VALUES(2);

You should get the following with no fragementation:

+-----+-------+
| uid | col_b |
+-----+-------+
|   1 |     1 |
|   2 |     2 |
+-----+-------+
2 rows in set (0.00 sec)

2. Manually Setting the Serial Column Value Can Cause Future Queries to Fail.

This was pointed out by @trev in a previous answer.

To simulate this manually set the uid to 4 which will "clash" later.

INSERT INTO table1 (uid, col_b) VALUES(5, 5);

Table data:

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
   5 |     5
(3 rows)

Run another insert:

INSERT INTO table1 (col_b) VALUES(6);

Table data:

 uid | col_b 
-----+-------
   1 |     1
   3 |     2
   5 |     5
   4 |     6

Now if you run another insert:

INSERT INTO table1 (col_b) VALUES(7);

It will fail with the following error message:

ERROR: duplicate key value violates unique constraint "table1_pkey" DETAIL: Key (uid)=(5) already exists.

In contrast, MySQL will handle this gracefully as shown below:

INSERT INTO table1 (uid, col_b) VALUES(4, 4);

Now insert another row without setting uid

INSERT INTO table1 (col_b) VALUES(3);

The query doesn't fail, uid just jumps to 5:

+-----+-------+
| uid | col_b |
+-----+-------+
|   1 |     1 |
|   2 |     2 |
|   4 |     4 |
|   5 |     3 |
+-----+-------+

Testing was performed on MySQL 5.6.33, for Linux (x86_64) and PostgreSQL 9.4.9

Wedge

You're giving a comparison but I don't see any solution here! Is it an answer? – Bran

@Bran it simply extends the various answers that state that the answer is to use a serial/sequence. This provides some important context to take into consideration. – Wedge

Coming from 10 years with MSSQL and Mysql, Point 2 is just utterly ridiculous. A random, manual insert from a user can entirely break an application – Thimbleful

@Thimbleful A code bug or data quality problem might sometimes legitimately call for a manual insert in a production situation. That might be enough of a reason for some application developers to avoid using this feature. – Doubleacting

@Thimbleful I agree this is bad, but "entirely break an application" is an overstatement. My testing shows that points 1 and 2 interact. That is, if you manually inserted id 5, you'll get an insertion failure when the autoincrement reaches 5, but that failure still increments the sequence. If you then retry the insert, it will succeed in creating the row with id 6. So the application isn't permanently broken, you just get a single insertion failure. – Constrain

"Failed Queries Increment The Sequence/Serial" - my testing with two psql instances shows that this also applies to failed transactions. If you begin two transactions, the first one to INSERT claims the first id. If the second transaction commits but the first rolls back, the first id will be skipped in the table. – Constrain

Answer 6

Sorry, to rehash an old question, but this was the first Stack Overflow question/answer that popped up on Google.

This post (which came up first on Google) talks about using the more updated syntax for PostgreSQL 10: https://blog.2ndquadrant.com/postgresql-10-identity-columns/

which happens to be:

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
);

Hope that helps :)

Apotheosis

This is indeed the way to go in PostgreSQL 10 and it is the same syntax as other database software like DB2 or Oracle. – Delao

@Delao Actually the GENERATED … AS IDENTITY commands are standard SQL. First added in SQL:2003, then clarified in SQL:2008. See features # T174 & F386 & T178. – Bragg

Answer 7

You have to be careful not to insert directly into your SERIAL or sequence field, otherwise your write will fail when the sequence reaches the inserted value:

-- Table: "test"

-- DROP TABLE test;

CREATE TABLE test
(
  "ID" SERIAL,
  "Rank" integer NOT NULL,
  "GermanHeadword" "text" [] NOT NULL,
  "PartOfSpeech" "text" NOT NULL,
  "ExampleSentence" "text" NOT NULL,
  "EnglishGloss" "text"[] NOT NULL,
  CONSTRAINT "PKey" PRIMARY KEY ("ID", "Rank")
)
WITH (
  OIDS=FALSE
);
-- ALTER TABLE test OWNER TO postgres;
 INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (1, '{"der", "die", "das", "den", "dem", "des"}', 'art', 'Der Mann küsst die Frau und das Kind schaut zu', '{"the", "of the" }');


 INSERT INTO test("ID", "Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (2, 1, '{"der", "die", "das"}', 'pron', 'Das ist mein Fahrrad', '{"that", "those"}');

 INSERT INTO test("Rank", "GermanHeadword", "PartOfSpeech", "ExampleSentence", "EnglishGloss")
           VALUES (1, '{"der", "die", "das"}', 'pron', 'Die Frau, die nebenen wohnt, heißt Renate', '{"that", "who"}');

SELECT * from test; 

Selle

Answer 8

Since PostgreSQL 10

CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);

Amabel

Answer 9

In the context of the asked question and in reply to the comment by @sereja1c, creating SERIAL implicitly creates sequences, so for the above example-

CREATE TABLE foo (id SERIAL,bar varchar);

CREATE TABLE would implicitly create sequence foo_id_seq for serial column foo.id. Hence, SERIAL [4 Bytes] is good for its ease of use unless you need a specific datatype for your id.

Jebel

Answer 10

This way will work for sure, I hope it helps:

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

INSERT INTO fruits(id,name) VALUES(DEFAULT,'apple');

or

INSERT INTO fruits VALUES(DEFAULT,'apple');

You can check this the details in the next link: http://www.postgresqltutorial.com/postgresql-serial/

Borges

Answer 11

Create Sequence.

CREATE SEQUENCE user_role_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 3
  CACHE 1;
ALTER TABLE user_role_id_seq
  OWNER TO postgres;

and alter table

ALTER TABLE user_roles ALTER COLUMN user_role_id SET DEFAULT nextval('user_role_id_seq'::regclass);

Starter