PostgreSQL in Depth

The PostgreSQL-specific features beyond standard SQL — richer data types, auto-incrementing ids, and the psql command-line client.

What is it?

Everything covered so far — SELECT, JOIN, WHERE — is standard SQL that works, with minor syntax differences, across most relational databases. PostgreSQL (often just "Postgres") is one of the most widely used databases, and it adds its own extensions on top of that standard worth knowing specifically: richer column types like JSONB (structured, queryable JSON stored in an efficient binary form) and arrays, auto-incrementing id columns, and psql, its interactive command-line client for talking to a database directly.

Explain like I'm 10

Standard SQL is like standard English, understood everywhere. Postgres-specific features are a rich regional vocabulary — genuinely more expressive if you're speaking with someone who knows it, but meaningless if you switch to a database that only speaks the standard dialect.

Examples

Postgres-specific column types

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  tags TEXT[],
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

SERIAL sets up an auto-incrementing integer id, TEXT[] stores a list of strings directly in one column, JSONB holds a flexible structured document, and TIMESTAMPTZ is a timezone-aware timestamp that defaults to the current time.

Querying inside a JSONB column

SELECT name, metadata->>'color' AS color
FROM products
WHERE metadata->>'in_stock' = 'true';

-> extracts a nested value as JSON, while ->> extracts it as plain text — here ->> is used because the color is being compared and displayed as ordinary text, not nested JSON.

How it works

JSONB isn't stored as the raw text you typed — Postgres parses it into a decomposed binary format up front, which is what lets it support indexing (a GIN index can index the keys/values inside a JSONB column) and fast operators like -> and ->>, at the cost of a small overhead when the value is first written. The psql client connects directly to a database (psql -h host -U user -d dbname) and gives you commands like \dt to list tables or \d products to inspect a table's columns — useful for quickly inspecting data or debugging without writing a script.

Why does it exist?

Real projects often need a mix of strict, well-known relational data (like a user's id and email) alongside genuinely flexible or per-row-variable data (like a product's optional custom attributes). JSONB lets Postgres handle both in one database, instead of needing a separate document database purely for the flexible parts.

When to use it

Use JSONB for a handful of genuinely flexible or sparse fields — custom attributes, a webhook payload, per-user settings. Use psql for quickly inspecting data, running ad-hoc queries, or debugging directly against a database during development.

When not to use it

Don't store your whole schema as JSONB just because it's flexible — you give up the enforcement, clarity, and full indexing support of proper columns and foreign keys. If a field has a known, stable shape and you regularly query or join on it, model it as a real column instead.

Common mistakes

  • Using -> (which returns JSON) when a plain text value was expected from ->> , leading to confusing type-mismatch errors in comparisons.

  • Reaching for a JSONB column to avoid designing a proper related table, out of laziness rather than genuine flexibility needs.

  • Assuming SERIAL ids are gapless — deleted rows leave permanent gaps, since the underlying sequence never goes backward.

Practice exercises

  1. Easy:

    Write a CREATE TABLE statement for an 'events' table with a SERIAL id and a JSONB 'payload' column.

  2. Medium:

    Write a query that selects rows from a JSONB 'settings' column where a nested key 'theme' equals 'dark'.

  3. Hard:

    Describe a scenario where you'd choose a JSONB column over creating a proper related table, and one where you'd choose the related table instead.

Interview questions

What's the difference between JSON and JSONB in PostgreSQL?

JSON is stored as the exact text you inserted; JSONB is parsed into a decomposed binary form, which is slightly slower to write but supports indexing and faster queries.

What does a SERIAL column do?

It creates an auto-incrementing integer column backed by a hidden sequence that generates each new row's id.

When would you reach for a JSONB column instead of normalizing into another table?

When the data's shape is genuinely variable or sparse per row and isn't something you heavily query or join on — otherwise a proper related table is usually the better fit.