PostgreSQL:CreateTable

lastmod 2026-10-27created 2021-03-0218 edits

An explanation of the Create Table statement in PostgreSQL.

AUTO INCREMENT and PRIMARY KEY

Use serial and PRIMARY KEY as follows.

id serial PRIMARY KEY,

postgresql then replaces it as follows.

id bigint NOT NULL DEFAULT nextval('seq_users_id'),
CONSTRAINT users_pkey PRIMARY KEY (id),

See PostgreSQL:Sequence#AUTO INCREMENT for details.

Data types

See PostgreSQL:DataTypes.

Constraints (CONSTRAINTS)

(PostgreSQL) Constraints, Constraint(NOT NULL, UNIQUE, PRIMARY KEY(primary key), FOREIGN KEY(foreign key))

Making multiple keys the primary key

CREATE TABLE EMP (
    EMP_NO CHAR(20) NOT NULL,
    EMP_NAME CHAR(20)     NOT NULL,
    SALARY    NUMBER(4)     NOT NULL,
    JOB   CHAR(10),
    EMAIL    VARCHAR(50),
    PRIMARY KEY(EMP_NO,EMP_NAME )
);

Making multiple keys UNIQUE

PostgreSQL UNIQUE Constraint

CREATE TABLE table (
    c1 data_type,
    c2 data_type,
    c3 data_type,
    UNIQUE (c2, c3)
);

Example

CREATE SEQUENCE IF NOT EXISTS seq_users_id
    MINVALUE 0
    START 0;

CREATE TABLE IF NOT EXISTS users(
    id bigint NOT NULL DEFAULT nextval('seq_users_id'),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    username VARCHAR(256) UNIQUE NOT NULL,
    password VARCHAR(256) NOT NULL,
    email VARCHAR(256) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP,
    last_login TIMESTAMP
);

Emptying data from all tables

See PostgreSQL:TruncateTable#Truncate all tables.

Favorite site

author Zer0 sitemap