SQL 2023 cheatsheet

SQL 2023: Tables and indexes

7 common SQL 2023 tasks.

Create a table

Define columns, a primary key, constraints and a foreign key.

CREATE TABLE orders (
  id         BIGINT PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users (id),
  status     TEXT NOT NULL DEFAULT 'new',
  total      NUMERIC(10, 2) NOT NULL CHECK (total >= 0),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Add a column

Add a column to an existing table.

ALTER TABLE users ADD COLUMN phone TEXT;

Rename a column

Rename a column without changing its data.

ALTER TABLE users RENAME COLUMN phone TO mobile;

Create an index

Speed up lookups and joins on a column.

CREATE INDEX idx_orders_user_id ON orders (user_id);

Enforce uniqueness

Reject duplicate values in a column.

CREATE UNIQUE INDEX idx_users_email ON users (email);

See how a query runs

Show the query plan to find out whether an index is used.

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

Drop a table

Delete a table and all its data.

DROP TABLE IF EXISTS temp_results;

Caution: Deletes the table and its data. Check the name and your connection first.