Tables

A table is the core building block. This page covers creating and removing tables; changing a table’s columns after it exists is covered in Columns.

Create a table (simple mode)

The simplest way to create a table is with pk on its own — it gives the table a ready-made primary-key column named id that the database fills in for you:

create table authors with pk

Prefer to name the key, or give it a specific type? Name it in the with pk clause:

create table authors with pk author_id(serial)

Either way, the rest of the columns are added afterwards with add column:

add column to authors: name (text)
add column to authors: birth_year (int)

Compound primary keys

To make the primary key span more than one column, list them, comma separated:

create table loans with pk book_id(int), member_id(int)

Syntax

create table <Name> with pk [<col>(<type>)[, ...]]

Create a table (advanced mode)

In advanced mode you write a full CREATE TABLE with every column and constraint inline:

create table authors (
  author_id serial primary key,
  name text not null,
  birth_year int
)

The advanced form also accepts table-level constraints and inline foreign keys — see Constraints and Relationships.

Syntax

create table [if not exists] <Name> (<col> <type> [constraints], ...)

Drop a table

drop table authors

In advanced mode you may add if exists so removing a table that is not there succeeds quietly:

drop table if exists authors

Renaming a table

Renaming a table is available in advanced mode:

alter table authors rename to writers

There is no simple-mode rename verb — switch to advanced mode (or use the one-line : escape) to rename a table.