Basics Updated November 2026

CREATE TABLE

CREATE TABLE name (column type constraints, ...) defines a new table: CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT, created_at DATE DEFAULT CURRENT_DATE). SQLite types are INTEGER, REAL, TEXT, BLOB; PostgreSQL adds VARCHAR(n), NUMERIC(10,2), BOOLEAN, DATE, TIMESTAMP and SERIAL or GENERATED ... AS IDENTITY for auto-increment ids.

Syntax

CREATE TABLE [IF NOT EXISTS] table_name (column1 type [constraints], column2 type [constraints], ... [, table constraints]);

Part	What it is
`NOT NULL`	The column must always have a value
`DEFAULT value`	Value used when an INSERT omits the column
`UNIQUE`	No two rows may share the same value
`PRIMARY KEY`	Unique, not null identifier of each row
`REFERENCES other(col)`	Foreign key: the value must exist in the other table

Examples

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  country TEXT DEFAULT 'Aruba'
);

Result: Table created (SQLite; INTEGER PRIMARY KEY auto-increments)

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  amount NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
  status VARCHAR(20) DEFAULT 'pending',
  order_date DATE DEFAULT CURRENT_DATE
);

Result: Table created (PostgreSQL)

In SQLite use id INTEGER PRIMARY KEY and REAL or NUMERIC for amount; the rest is the same.

CREATE TABLE IF NOT EXISTS big_spenders AS
SELECT customer_id, SUM(amount) AS spent FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000;

Result: New table filled from the query

CREATE TABLE AS copies data and column types but not constraints or keys.

Common mistakes

Table already exists error on rerun; add IF NOT EXISTS or DROP TABLE IF EXISTS first in setup scripts.
Using AUTO_INCREMENT (MySQL) or IDENTITY(1,1) (SQL Server) in SQLite or PostgreSQL; use INTEGER PRIMARY KEY (SQLite) or SERIAL / GENERATED ALWAYS AS IDENTITY (PostgreSQL).
Forgetting that SQLite does not enforce foreign keys unless PRAGMA foreign_keys = ON is set for the connection.

Practice it

The drills on Querydrill check your answer on live data, free, no account needed.

Start practicing

Questions

What data types should I use?

Whole numbers: INTEGER. Money: NUMERIC(10,2) in PostgreSQL (SQLite stores it as REAL or TEXT, so store cents as INTEGER if exactness matters). Text: TEXT or VARCHAR(n). Dates: DATE / TIMESTAMP in PostgreSQL, ISO text in SQLite.

How do I add a column to an existing table?

ALTER TABLE customers ADD COLUMN phone TEXT. Both databases support it; SQLite limits what else ALTER TABLE can do (rename and drop column are supported in recent versions).

How do I delete a table?

DROP TABLE IF EXISTS table_name. It removes the table and all its data permanently.

Primary and foreign keysA primary key uniquely identifies each row; a foreign key points to a primary key in another table so the link stays valid. Declaring both, composite keys.
INSERT INTOINSERT INTO adds rows to a table. Syntax with a column list, inserting multiple rows in one statement, INSERT ... SELECT to copy data, RETURNING the new id.
DELETEDELETE FROM table WHERE condition removes matching rows. Delete by id, by subquery, duplicates, everything in a table, and DELETE versus TRUNCATE.
CASTCAST(value AS type) converts between types: string to integer, integer to decimal for division, number to text for concatenation, text to date, plus :: form.