Postgres by Example: CREATE TABLE

Tables are how PostgreSQL stores your data. CREATE TABLE defines a new table by listing its columns and their data types. Defaults, constraints, indexes, and foreign keys can all be added at create time, but a minimal table needs only a name and a column list. This lesson keeps things small; constraints get their own lessons.

What you'll learn:

• Defining a table with CREATE TABLE
• Picking a data type for each column (integer, text, etc.)
• Making the create script idempotent with DROP TABLE IF EXISTS
• Inspecting tables in the public schema
• The difference between TEMP TABLE, regular tables, and UNLOGGED TABLE

-- Drop the table if it exists so the script is idempotent
DROP TABLE IF EXISTS fruits;

-- A minimal table: id and name
CREATE TABLE fruits (
  id   integer,
  name text
);

-- Confirm it exists in the current schema
SELECT schemaname, tablename
FROM pg_tables
WHERE tablename = 'fruits';

-- See the columns and their types
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'fruits'
ORDER BY ordinal_position;

fruits has two columns: id (a 32-bit signed integer) and name (variable-length text). The order of columns in CREATE TABLE is the physical order rows are stored in — for storage efficiency on wide tables, group fixed-width columns together. For small tables it doesn't matter.

DROP TABLE IF EXISTS removes the table only if it exists; without IF EXISTS, the statement would error on a fresh database. Combined with CREATE TABLE, you get a script you can rerun safely while you iterate on the schema. In production, of course, do not blindly drop tables — use migrations.

There are several variants of CREATE TABLE worth knowing about:

• CREATE TEMP TABLE (or CREATE TEMPORARY TABLE) creates a table that exists only for the current session and is dropped automatically.
• CREATE UNLOGGED TABLE skips the write-ahead log — faster writes, but the contents are wiped if the server crashes. Useful for derived/staging data you can rebuild.
• CREATE TABLE ... (LIKE other_table INCLUDING ALL) clones the schema of another table.
• CREATE TABLE ... AS SELECT ... creates and populates a table from a query (called a CTAS).

To run:

$ psql -f source/create-table.sql postgres
DROP TABLE
CREATE TABLE
 schemaname | tablename
------------+-----------
 public     | fruits
(1 row)

 column_name | data_type | is_nullable
-------------+-----------+-------------
 id          | integer   | YES
 name        | text      | YES
(2 rows)

Common pitfalls:

• Picking varchar(255) because that's what you learned in MySQL: in PostgreSQL, text and varchar(n) have the same performance. Use text unless you specifically want a length cap.
• Forgetting NOT NULL on columns that should never be null — you can add it later, but it is easier to set up correctly from the start.
• Naming a column user, order, select — these are reserved words. Either rename (user_id, placed_at) or quote ("user"), but the second is friction forever.

Tip: Standard column types worth memorizing: integer (32-bit), bigint (64-bit), text, boolean, date, timestamp with time zone (almost always preferred over timestamp), numeric(p,s) for exact decimals, and uuid for opaque IDs. Each gets its own lesson.

Try it: Add a third column, e.g. price numeric(10,2), and run the script again. Then change integer to bigint and rerun. Inspect with \d fruits in an interactive psql session.

Source: create-table.sql

Next: INSERT

Home: Postgres by Example