PostgreSQL Tables and Data Types

Schemas and Data / Guide 6 of 47 / Beginner

• OSUbuntu 26.04 LTS (resolute)
• PostgreSQL18.6-0ubuntu0.26.04.1
• Server timezoneEtc/UTC
• TimeAbout 15 min
• Reviewed27 August 2026

Written against the versions above. `GENERATED ALWAYS AS IDENTITY` is the SQL-standard form and has been available since PostgreSQL 10. `serial` still works and is still what most older tutorials show, but it leaves a sequence that outlives the column.

Before you start

• The appdb database from the foundations track.

1. 1

   Create a table with types chosen on purpose

   Every column here is a decision worth defending.

   bigint GENERATED ALWAYS AS IDENTITY rather than serial. Identity is the SQL standard, the sequence it owns is dropped with the column, and ALWAYS refuses a manually supplied value - which the constraints guide demonstrates. bigint because running out of int at two billion is a genuinely awful day.

   text rather than varchar(80). In PostgreSQL they are the same type underneath with no performance difference, so a length limit is a business rule. Add one with a CHECK when you actually have a rule; do not invent 80 as a guess you cannot easily change later.

   numeric(10,2) for money, exact by definition, never real or double.

   text[] and jsonb are native types with no MySQL equivalent - an array column and a binary JSON column, both indexable and both queryable in SQL. The last guide in this track uses them properly.

   timestamptz, always, for reasons the timestamps guide makes unarguable.

   $psql -d appdb -c "CREATE TABLE customers (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE, credit numeric(10,2) NOT NULL DEFAULT 0, tags text[] NOT NULL DEFAULT '{}', profile jsonb NOT NULL DEFAULT '{}', created_at timestamptz NOT NULL DEFAULT now())"CREATE TABLE

   Expected resultCREATE TABLE.

   Success conditionYou have a table whose types were chosen rather than defaulted to.

2. 2

   Read the table back

   \d is psql's table description and is more informative than MySQL's DESCRIBE: it shows types, nullability, defaults and the indexes and constraints underneath.

   Two things to notice. The identity column's default reads as generated always as identity rather than a sequence name - the sequence is owned and hidden. And the UNIQUE on email has become a named index, customers_email_key, following PostgreSQL's automatic naming convention: table, column, suffix. That name appears verbatim in the error message when it fires, which is what makes those errors so easy to act on.

   $psql -d appdb -c "\d customers"                                  Table "public.customers"   Column   |           Type           | Collation | Nullable |           Default------------+--------------------------+-----------+----------+------------------------------ id         | bigint                   |           | not null | generated always as identity name       | text                     |           | not null | email      | text                     |           | not null | credit     | numeric(10,2)            |           | not null | 0 tags       | text[]                   |           | not null | '{}'::text[] profile    | jsonb                    |           | not null | '{}'::jsonb created_at | timestamp with time zone |           | not null | now()Indexes:    "customers_pkey" PRIMARY KEY, btree (id)    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

   Expected resultSeven columns with their types and defaults, plus the primary key and unique indexes.

   Success conditionYou can read a table's full definition including its constraints.

3. 3

   Insert rows and get something back

   RETURNING is the feature to take away from this step. The insert returns the generated ids in the same round trip - no LAST_INSERT_ID(), no second query, and it works for multi-row inserts where a last-id function cannot.

   It works on UPDATE and DELETE too, which makes "change this and tell me what you changed" a single atomic statement.

   TABLE customers is shorthand for SELECT * FROM customers. The array and jsonb columns display as {vip,eu} and {"tier": "gold", ...} - stored as structured values, not as strings that happen to look like them.

   $psql -d appdb -c "INSERT INTO customers (name, email, credit, tags, profile) VALUES ('Ada Lovelace','ada@example.com',120.50,'{vip,eu}','{\"tier\":\"gold\",\"seats\":3}'), ('Grace Hopper','grace@example.com',0,'{us}','{\"tier\":\"silver\",\"seats\":1}'), ('Zoe Washburne','zoe@example.com',75.25,'{apac}','{\"tier\":\"gold\",\"seats\":9}') RETURNING id, name" id |     name----+---------------  1 | Ada Lovelace  2 | Grace Hopper  3 | Zoe Washburne(3 rows) INSERT 0 3$psql -d appdb -c "CREATE TABLE customers (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, email text NOT NULL UNIQUE, credit numeric(10,2) NOT NULL DEFAULT 0, tags text[] NOT NULL DEFAULT '{}', profile jsonb NOT NULL DEFAULT '{}', created_at timestamptz NOT NULL DEFAULT now())"CREATE TABLE

   Expected resultThree ids returned, then the full rows including arrays and jsonb.

   Success conditionYou can insert and read back generated values in one statement.

Troubleshooting

• Coming from MySQL and looking for AUTO_INCREMENT.

  Why: PostgreSQL uses sequences, exposed as serial or identity columns.

  Fix:GENERATED ALWAYS AS IDENTITY for new tables. serial works but leaves an orphaned sequence if the column is dropped.

• Wondering whether varchar(n) is faster than text.

  Why: A habit carried from other databases.

  Fix:It is not. They are the same storage; varchar(n) only adds a length check. Use text plus a CHECK when you have a real rule.

• LAST_INSERT_ID() does not exist.

  Why: It is a MySQL function.

  Fix:RETURNING id, which is better - it works for multi-row inserts.

Official sources

• PostgreSQL 18 Documentation - Data Types
• PostgreSQL 18 Documentation - Date/Time Types
• PostgreSQL 18 Documentation - Constraints
• PostgreSQL 18 Documentation - INSERT ... ON CONFLICT
• PostgreSQL 18 Documentation - JSON Types and Functions
• PostgreSQL 18 Documentation - Arrays