PostgreSQL autoincrement

How to create PostgreSQL autoincrement column?

1. CREATE TABLE with SERIAL type

When you are creating a table, you can use the data type SERIAL to define auto-increment columns:

CREATE TABLE table_name (
id SERIAL,
next_column varchar);

Then insert values:

INSERT INTO table_name (next_column) values ('value1');
INSERT INTO table_name (next_column) values ('value2');

And the values in the id column will be auto generated.

2. CREATE SEQUENCE

That CREATE TABLE statement using SERIAL type above is equivalent to this combination of statements:

CREATE SEQUENCE id_seq;
CREATE TABLE table_name (
    id smallint NOT NULL DEFAULT nextval('id_seq'),
    next_column varchar
);
ALTER SEQUENCE id_seq OWNED BY table_name.id;

---

Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS