Creating Tables

Tables are the heart of every PostgreSQL database. Every piece of data you store, query, and analyse lives in a table. In this lesson you will learn how to design and create tables properly — choosing column names, assigning the right data types, and building the five tables that make up the Dataplexa Store dataset you already have loaded. Understanding how these tables were built gives you the foundation to design your own tables confidently from scratch.

The CREATE TABLE Statement

You create a table using CREATE TABLE followed by the table name and a list of column definitions inside parentheses. Each column definition has three parts — the column name, the data type, and any optional constraints. The statement ends with a semicolon.

-- Basic structure of CREATE TABLE
CREATE TABLE table_name (
  column_name  data_type  constraints,
  column_name  data_type  constraints,
  column_name  data_type  constraints
);

-- Simple example — a basic products table
CREATE TABLE products (
  id          SERIAL        PRIMARY KEY,  -- auto-incrementing unique ID
  name        VARCHAR(100)  NOT NULL,     -- product name, required
  price       NUMERIC(10,2) NOT NULL,     -- price with 2 decimal places, required
  stock       INTEGER       DEFAULT 0,    -- quantity in stock, defaults to 0
  is_active   BOOLEAN       DEFAULT TRUE  -- whether product is listed, defaults to true
);

A few things to notice here. SERIAL PRIMARY KEY on the id column means PostgreSQL automatically generates a unique number for every new row — you never have to supply an ID manually. NOT NULL means the column must always have a value — you cannot leave it empty. DEFAULT 0 means if you do not supply a value when inserting, PostgreSQL fills it in with 0 automatically.

Checking a Table Was Created

After creating a table you can immediately inspect its structure in psql using \d tablename. This shows every column, its type, whether it allows nulls, and any defaults or constraints.

-- Inspect the table structure
\d products

Creating the Dataplexa Store Tables

Now let us walk through the actual tables in your course dataset. This shows you real-world table design — how columns are chosen, why certain types are used, and how tables are structured to work together. Even though these tables are already loaded in your database, reading through the CREATE statements helps you understand the design decisions behind them.

The customers table stores everyone who has made a purchase. Every customer has a unique ID, contact details, their US state, and a record of when they joined.

CREATE TABLE customers (
  id           SERIAL        PRIMARY KEY,           -- unique customer ID, auto-generated
  first_name   VARCHAR(50)   NOT NULL,              -- first name, required
  last_name    VARCHAR(50)   NOT NULL,              -- last name, required
  email        VARCHAR(100)  NOT NULL UNIQUE,       -- email must be unique across all customers
  city         VARCHAR(100),                        -- city, optional
  state        VARCHAR(2),                          -- US state code e.g. 'CA', 'NY'
  joined_date  DATE          DEFAULT CURRENT_DATE   -- date they signed up, defaults to today
);

Notice the UNIQUE constraint on email — no two customers can share the same email address. The state column uses VARCHAR(2) because US state codes are always exactly two characters like CA, NY, or TX. The joined_date defaults to CURRENT_DATE so new customers are automatically stamped with today's date.

The products table stores the store's catalogue — every item available for purchase with its price, category, and stock level.

CREATE TABLE products (
  id           SERIAL         PRIMARY KEY,          -- unique product ID
  name         VARCHAR(100)   NOT NULL,             -- product name
  category     VARCHAR(50),                         -- e.g. 'Electronics', 'Office'
  price        NUMERIC(10,2)  NOT NULL,             -- exact price in USD
  stock_qty    INTEGER        DEFAULT 0,            -- units available, defaults to 0
  is_active    BOOLEAN        DEFAULT TRUE          -- whether the product is listed
);

The employees table stores everyone who works at the store — their role, department, salary, and start date.

CREATE TABLE employees (
  id           SERIAL        PRIMARY KEY,           -- unique employee ID
  first_name   VARCHAR(50)   NOT NULL,              -- first name
  last_name    VARCHAR(50)   NOT NULL,              -- last name
  email        VARCHAR(100)  NOT NULL UNIQUE,       -- work email, must be unique
  department   VARCHAR(50),                         -- e.g. 'Sales', 'Engineering'
  job_title    VARCHAR(100),                        -- e.g. 'Account Manager'
  salary       NUMERIC(10,2),                       -- annual salary in USD
  hire_date    DATE          DEFAULT CURRENT_DATE   -- date they were hired
);

The orders table records every purchase made. It links to the customers table using a customer_id foreign key — this is the relationship that lets you answer questions like "show me all orders placed by customers in Texas."

CREATE TABLE orders (
  id            SERIAL        PRIMARY KEY,          -- unique order ID
  customer_id   INTEGER       NOT NULL,             -- links to customers.id
  order_date    DATE          DEFAULT CURRENT_DATE, -- when the order was placed
  status        VARCHAR(20)   DEFAULT 'pending',    -- 'pending', 'shipped', 'delivered'
  total_amount  NUMERIC(10,2),                      -- total value of the order in USD
  FOREIGN KEY (customer_id) REFERENCES customers(id)  -- enforces the link to customers
);

The FOREIGN KEY line at the bottom is what creates the relationship between orders and customers. PostgreSQL will now reject any order that references a customer_id that does not exist in the customers table — keeping your data consistent.

The order_items table stores the individual line items within each order — which product was purchased, how many, and at what price. One order can have many order items.

CREATE TABLE order_items (
  id          SERIAL        PRIMARY KEY,            -- unique line item ID
  order_id    INTEGER       NOT NULL,               -- links to orders.id
  product_id  INTEGER       NOT NULL,               -- links to products.id
  quantity    INTEGER       NOT NULL DEFAULT 1,     -- how many units, at least 1
  unit_price  NUMERIC(10,2) NOT NULL,               -- price at time of purchase
  FOREIGN KEY (order_id)   REFERENCES orders(id),  -- link to the order
  FOREIGN KEY (product_id) REFERENCES products(id) -- link to the product
);

This table has two foreign keys — one linking to orders and one linking to products. This is how a many-to-many relationship between orders and products is handled in a relational database. Each row in order_items is one product line within one order.

IF NOT EXISTS — Safe Table Creation

If you run a CREATE TABLE statement for a table that already exists, PostgreSQL throws an error. Adding IF NOT EXISTS makes it safe — PostgreSQL silently skips the creation if the table is already there. This is useful in setup scripts that might be run more than once.

-- Safe version — no error if table already exists
CREATE TABLE IF NOT EXISTS products (
  id    SERIAL       PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  price NUMERIC(10,2)
);

Creating a Table from Another Table

PostgreSQL lets you create a new table by copying the structure and data from an existing query. This is useful for creating summary tables, backup copies, or test tables with a subset of real data.

-- Create a new table from a SELECT query
-- This copies both structure AND data
CREATE TABLE high_value_orders AS
SELECT id, customer_id, total_amount, order_date
FROM orders
WHERE total_amount > 500;

-- Check what was created
SELECT COUNT(*) FROM high_value_orders;

Dropping a Table

Dropping a table permanently deletes it along with all its data. There is no undo. If other tables have foreign keys pointing to the table you are dropping, PostgreSQL will refuse to drop it unless you use CASCADE.

-- Drop a table permanently
DROP TABLE high_value_orders;

-- Drop only if it exists — no error if it does not
DROP TABLE IF EXISTS high_value_orders;

-- Drop a table and all tables that depend on it
DROP TABLE customers CASCADE;

The Dataplexa Store — Table Relationships at a Glance

Next up: Constraints and keys — the rules that keep your data clean and trustworthy.