How to create tables with SQL — Basic

SQL / Knex Raw SQL

Define schemas with CREATE TABLE for columns, types, and constraints.

Learn: Tables define your domain model in PostgreSQL. Choose types deliberately (TEXT vs VARCHAR, TIMESTAMPTZ for times). Primary keys and foreign keys document relationships and enable referential integrity.

Supplementary examples

Users and pastes tables

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL UNIQUE
);

CREATE TABLE pastes (
  paste_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(user_id),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Course example

-- First, remove the table if it exists
DROP TABLE IF EXISTS customers;
-- Create the table anew
CREATE TABLE customers (
customer_id INTEGER primary key generated by default as identity,
customer_name varchar NOT NULL,
phone VARCHAR(30)
);
-- Insert some test data
-- using a multi-row insert statement here
INSERT INTO customers (customer_name, phone)
VALUES
('Sam', '555-1234'),
('Ham', '555-4321'),
('Ram', '555-5678');

Additional references & examples

PostgreSQL documentation
SQL syntax, data types, and database administration reference.
CREATE TABLE
PostgreSQL table definition syntax.