By the end of this lesson you can write a CREATE TABLE statement with typed columns, NOT NULL rules and a primary key, and confirm the result in the system catalog.
Why it matters: Every table you have queried so far was created by someone with CREATE TABLE. Analysts create tables for staging data, lookup lists and snapshots; developers create them for every new feature. Getting the definition right first saves painful fixes later.
Dataset: Starter shop. Examples run in the editor on the right.
Until now you have only read data. This track is about defining and changing it. The statements split into two families:
- DDL (data definition language) changes the structure:
CREATE TABLE,ALTER TABLE,DROP TABLE,CREATE VIEW. - DML (data manipulation language) changes the rows:
INSERT,UPDATE,DELETE.
A table definition is a contract. It says which columns exist, what kind of value each one holds, and which rules every row must obey. PostgreSQL enforces that contract on every insert and update, forever. A well-designed table makes bad data impossible; a sloppy one lets it in quietly, and someone discovers the mess months later in a report.
CREATE TABLE table_name (
column_name data_type [column rules],
column_name data_type [column rules],
...
);Each column needs a name (lower case with underscores is the PostgreSQL convention: supplier_name, not SupplierName) and a data type, which decides what values are allowed and how they behave: integer for whole numbers, numeric(12,2) for money, text for strings, date for calendar dates, boolean for true/false. The next lesson covers types in depth.
After the type you can add rules (constraints):
NOT NULL: the column must always have a value. Without it, NULL is allowed.PRIMARY KEY: the column identifies the row. It must be unique and is automatically NOT NULL. A table should almost always have one.
A new table is empty. Creating it changes nothing in the existing tables.
CREATE TABLE suppliers (
supplier_id integer PRIMARY KEY,
supplier_name text NOT NULL,
city text,
phone text
);
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'suppliers';The CREATE TABLE statement returns no rows, it only reports success. The SELECT then asks the system catalog to describe the new table: four columns, with is_nullable = NO for supplier_id (primary keys are never NULL) and supplier_name (declared NOT NULL), and YES for city and phone.
Checking what you created
PostgreSQL describes itself in tables too. information_schema.columns has one row per column of every table: its name, data_type, is_nullable, column_default and more. information_schema.tables lists the tables themselves. These catalog views are standard SQL, so the same queries work in MySQL and SQL Server. Get into the habit of checking a definition after you create it, the same way you check a query's result.
The table exists for the rest of the session. Run the same CREATE TABLE again and PostgreSQL refuses with relation "suppliers" already exists. CREATE TABLE IF NOT EXISTS skips the statement quietly instead, which is useful in setup scripts that may run twice. Be careful with it: if an older table with the same name but different columns exists, IF NOT EXISTS keeps the old one and says nothing.
CREATE TABLE suppliers (
supplier_id integer PRIMARY KEY,
supplier_name text NOT NULL,
city text,
phone text
);
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (1, 'Najd Coffee Roasters', 'Riyadh');
SELECT * FROM suppliers;The INSERT adds one row (you'll learn INSERT properly in lesson 6). phone was not given, so it is NULL, which the definition allows. The final SELECT shows exactly one row. Trying to insert a row without a supplier_name would fail with a not-null violation.
Creating a table from a query
CREATE TABLE new_table AS SELECT … (often called CTAS) creates a table and fills it with a query's result in one step. The column names come from the query (so alias computed columns) and the types are inferred. It's handy for snapshots and staging tables. It copies data only: no primary key, no NOT NULL rules, no defaults. If the new table matters, add those rules afterwards.
CREATE TABLE stock_snapshot AS
SELECT category, sum(stock) AS units_in_stock
FROM products
GROUP BY category;
SELECT * FROM stock_snapshot ORDER BY category;The new table has one row per category (five on this data) with the columns category and units_in_stock. units_in_stock has type bigint because sum() of integers returns bigint. The snapshot does not change when products change later: it is a copy, not a live view.