CREATE and ALTER

Written Explanation

CREATE TABLE

The general shape:

CREATE TABLE table_name (
  column_name  type  [constraints],
  column_name  type  [constraints],
  ...
);

Each column gets a name, a type, and optional constraints: rules the database enforces. Columns are separated by commas, and there is no comma after the last one.

A first complete example, from the library:

CREATE TABLE authors (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  country TEXT
);

Reading it line by line: id holds whole numbers and is the primary key (unique, never empty); name holds text and must be filled in; country holds text and may be empty (NULL).

Types you need now

PostgreSQL has many more types (VARCHAR(n) for text with a length limit, TIMESTAMP for date and time, BIGINT for large whole numbers). Other databases name and treat some types differently, so check your product. Choose the type that matches the meaning: a year is a number, a date is a DATE, never a text like '4 May', because the database can then compare and sort it correctly.

Constraints you need now

An example with a foreign key:

CREATE TABLE books (
  id             INTEGER PRIMARY KEY,
  title          TEXT NOT NULL,
  author_id      INTEGER NOT NULL REFERENCES authors (id),
  published_year INTEGER
);

author_id can only hold an id that exists in authors. That is what keeps a book from pointing at an author who does not exist.

Order matters. A foreign key points at a table that must already exist, so create the "one" side before the "many" side: authors before books, and books and members before loans.

ALTER TABLE

Requirements change after a table is created. ALTER TABLE changes the definition without recreating the table. The PostgreSQL manual lists these operations: add columns, remove columns, add and remove constraints, change default values, change data types, rename columns, and rename tables. The common ones:

ALTER TABLE publishers ADD COLUMN website TEXT;
ALTER TABLE publishers RENAME COLUMN city TO hq_city;
ALTER TABLE publishers DROP COLUMN founded;

Adding a column to a table that already has rows. The new column appears in every existing row. It holds the DEFAULT if you gave one, otherwise NULL. That has a consequence: ADD COLUMN ... NOT NULL with no default cannot work on a table that already holds rows, because the existing rows would have to be NULL. The fix is to give a default (or to add the column as nullable, fill it with UPDATE, then add NOT NULL).

DROP COLUMN deletes that column's data in every row. Nothing asks "are you sure".

DROP TABLE

DROP TABLE publishers;

This removes the table, its structure, and all of its rows. It is the DDL statement to treat with the most respect. PostgreSQL will refuse if another table's foreign key still depends on the target, and says so; the hint offers CASCADE, which also removes the dependent constraints. Do not reach for CASCADE just to make an error go away: read what it would remove first.

CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS skip the work (with a notice) instead of raising an error. They are handy in setup scripts, but they can also hide the fact that a table was not what you thought it was.