ruk·si

🐘 PostgreSQL
CREATE TABLE

Updated at 2015-05-02 14:17

This note contains notes about creating tables in PostgreSQL.

Related to:

Table with a single column as the primary key. PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. No need to create your own.

--  DROP TABLE users;
CREATE TABLE users (
  user_id     uuid        NOT NULL DEFAULT uuid_generate_v4(),
  name        text        NOT NULL,
  created_at  timestamp   DEFAULT clock_timestamp(),
  updated_at  timestamp   DEFAULT clock_timestamp(),
  CONSTRAINT  users_pkey  PRIMARY KEY (user_id)
);
-- note that `timestamp` means `timestamp without time zone`

INSERT INTO users
    (name)
VALUES
    ('John Doe'),
    ('Conan Barb'),
    ('Mary Hoover'),
    ('Mary Curé'),
    ('Seppo Ättö');

Table with multiple column primary key.

CREATE TABLE films (
    code            text,
    title           text,
    id              integer,
    release_date    date,
    kind            text,
    CONSTRAINT films_pkey PRIMARY KEY (code, title)
);

Column with a default value. If the column is omitted from INSERT statement, will use the default.

CREATE TABLE distributors (
    name        text        DEFAULT 'Luso Films',
    id          integer     DEFAULT nextval('distributors_serial'),
    change_time timestamp   DEFAULT clock_timestamp()
);

Unique constraint. Enforces that the specified row column is unique on the whole table.

-- column constraint
CREATE TABLE distributors (
    id      integer,
    name    text        UNIQUE
);

-- table constraint
CREATE TABLE distributors (
    id      integer,
    name    text
    UNIQUE  (name)
);

Custom check constraint. You can also specify your own validity checks.

-- column constraint
CREATE TABLE distributors (
    id      integer CHECK (id > 100),
    name    text
);

-- table constraint
CREATE TABLE distributors (
    id      integer,
    name    text
    CONSTRAINT is_valid CHECK (id > 100 AND name <> '')
);

Foreign key column. Foreign keys refer to columns in other tables so the referenced row and column must exist.

CREATE TABLE orders (
    order_id    integer     PRIMARY KEY,
    product_no  integer     REFERENCES products (product_no),
    quantity    integer
);

-- implicitly use the primary key of the foreign table
CREATE TABLE orders (
    order_id    integer     PRIMARY KEY,
    product_no  integer     REFERENCES products,
    quantity    integer
);

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

You can customize foreign key deletion behavior with ON DELETE.

CREATE TABLE products (
    product_no          integer     PRIMARY KEY,
    name                text,
    price               numeric
);

CREATE TABLE orders (
    order_id            integer     PRIMARY KEY,
    shipping_address    text,
);

-- ON DELETE NO ACTION prevent deletion if not handled by the end of transaction
-- ON DELETE RESTRICT prevents deletion if this exists
-- ON DELETE CASCADE if referenced row is removed, this row is also removed
-- ON DELETE SET NULL if referenced row is removed, set this to null
-- ON DELETE SET DEFAULT if referenced row is removed, set this to column default
CREATE TABLE order_items (
    product_no          integer REFERENCES products ON DELETE RESTRICT,
    order_id            integer REFERENCES orders   ON DELETE CASCADE,
    quantity            integer,
    PRIMARY KEY (product_no, order_id)
);

What is OID? Sometimes you might see term OID in database definitions. OIDs basically give you a built-in, globally unique id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key. It's kinda deprecated so keep it disabled if you don't have a very good reason to use it.

Sources