🐘 PostgreSQL - CREATE TABLE
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.