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

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.

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.

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

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

Since PostgreSQL 10

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

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/

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);