postgres identifier primary key

integer/biginteger

CREATE TABLE users (
  id integer PRIMARY KEY,
  name text NOT NULL
);
INSERT INTO users (id, email, name)
SELECT COUNT(*) + 1, 'new_user' FROM users;

serial/bigserial

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

Using a serial column to create the users table would look like this:

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO users_serial (name) VALUES ('new user');

Using a serial column is operationally similar to the following SQL:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

When writing automation that simply iterates through id values, note that serial columns can have gaps, even if you never DELETE (e.x. if an INSERT was rolled back — sequences live outside transactions).

serial is PostgreSQL specific (i.e. not SQL standards compliant) but it solved in Postgres 10. Postgres 10 added support for the IDENTITY column syntax in CREATE TABLE

https://www.enterprisedb.com/blog/postgresql-10-identity-columns-explained

CREATE TABLE (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  email citext NOT NULL CHECK (LENGTH(email) < 255),
  name text NOT NULL
)