CREATE TABLE

So far, you have been querying tables that already exist. Now it's time to build your own! CREATE TABLE defines a new table with columns, types, and constraints.

You can create up to 3 tables in the sandbox. They appear under "Your Tables" in the sidebar. Tables are automatically cleaned up after 24 hours.

Basic Syntax

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    ...
);

Try It — Create Your First Table

Run this in the SQL editor:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    enrolled BOOLEAN DEFAULT TRUE
);

After creating it, query your new table:

SELECT * FROM students;

Then insert some data:

INSERT INTO students (name, email, age) VALUES
  ('Alice', 'alice@school.edu', 20),
  ('Bob', 'bob@school.edu', 22);

Column Definitions

Each column has three parts:

1. Name — the identifier used in queries
2. Data type — what kind of data it stores
3. Constraints — rules the data must follow (optional)

Common Data Types

Constraints

NOT NULL — Column must have a value

CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    phone VARCHAR(20)  -- optional
);

UNIQUE — No duplicate values

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

PRIMARY KEY — Unique + NOT NULL

Every table should have one. It uniquely identifies each row:

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

DEFAULT — Auto-fill when no value given

CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CHECK — Validate values

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) CHECK (price >= 0),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5)
);

IF NOT EXISTS

Prevents errors when the table already exists:

CREATE TABLE IF NOT EXISTS logs (
    id SERIAL PRIMARY KEY,
    message TEXT NOT NULL
);

Try It Yourself

1. Create a movies table with: id (SERIAL PRIMARY KEY), title (TEXT NOT NULL), director (VARCHAR(200)), release_year (INTEGER), rating (NUMERIC(3,1) CHECK between 1 and 10)

2. Insert a few movies and query them

3. Try creating the same table again — what happens? Now try with IF NOT EXISTS

Remember: you can create up to 3 tables. Use DROP TABLE table_name; to remove one if you need space.