CREATE TABLE in PostgreSQL

CREATE TABLE defines a table's structure — its columns, their types, and any rules those columns must follow.

Basic Syntax

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

Example

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  department VARCHAR(50) DEFAULT 'Unassigned',
  salary NUMERIC(10, 2),
  hired_at TIMESTAMPTZ DEFAULT now()
);

• SERIAL PRIMARY KEY — auto-incrementing integer ID, uniquely identifying each row.
• NOT NULL — the column must always have a value.
• UNIQUE — no two rows can share the same value in this column.
• DEFAULT — the value used automatically when one isn't provided on insert.

Avoiding "Already Exists" Errors

CREATE TABLE IF NOT EXISTS employees ( ... );

Creating a Table from a Query

Copy both the structure and the data of an existing table (or query) into a new one:

CREATE TABLE senior_employees AS
SELECT * FROM employees WHERE salary > 80000;

Inspecting a Table

\d employees

                                        Table "public.employees"
  Column    |           Type           | Nullable |              Default
------------+--------------------------+----------+-----------------------------------
 id         | integer                  | not null | nextval('employees_id_seq'::regclass)
 name       | character varying(100)   | not null |
 email      | character varying(150)   | not null |
 department | character varying(50)    |          | 'Unassigned'::character varying
 salary     | numeric(10,2)            |          |
 hired_at   | timestamp with time zone |          | now()

Common Errors & Troubleshooting

• ERROR: relation "employees" already exists — use CREATE TABLE IF NOT EXISTS to avoid this when re-running a setup script, or DROP TABLE IF EXISTS employees; first if you genuinely want to recreate it.
• ERROR: syntax error at or near ")" — almost always a trailing comma after the last column definition, or a missing comma between two columns — check both ends of the column list.
• A NOT NULL column rejecting inserts you thought were fine — remember DEFAULT only fills in a value when the column is omitted from the INSERT; explicitly inserting NULL still violates NOT NULL, default or not.

Key Takeaways

• Every column needs a data type; constraints (NOT NULL, UNIQUE, etc.) are optional but recommended.
• SERIAL is the standard way to get an auto-incrementing primary key.
• CREATE TABLE AS SELECT (CTAS) is a quick way to snapshot query results into a new table.