# CREATE TABLE

> Create a table, inspect its columns, and understand temporary and unlogged tables.

---

LLMS index: [llms.txt](/en/llms.txt)

---

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 {#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`

## Example {#example}

```sql {num="16.1" caption="CREATE 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*).

## Run the example {#run}

To run:

```text
$ 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 {#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] 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.

> [!NOTE] 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.

## Downloads {#downloads}

SQL file: [create-table.sql](/examples/source/create-table.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)