psql and the basics of SQL

Drive the server from psql, learn the meta-commands worth memorising, and create tables with the types PostgreSQL actually gives you.

Getting comfortable in psql

psql is the official client and it is worth learning properly: the backslash meta-commands introspect a live database faster than any GUI, and they are available on every server you will ever ssh into.

psql -h 10.0.0.20 -p 5432 -U app -d appdb
psql "postgresql://app:[email protected]:5432/appdb?sslmode=require"
psql -c "SELECT now();" appdb          # one statement, good in scripts
psql -f schema.sql appdb               # run a file
PGHOST=10.0.0.20 PGUSER=app psql appdb # env vars instead of flags

Identifiers are folded to lower case unless you quote them, so CREATE TABLE Orders creates orders while "Orders" creates a table whose name you must quote forever after. Prefer plain lower-case snake_case names.

Creating tables with real types

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,
  total       numeric(12,2) NOT NULL CHECK (total >= 0),
  status      text NOT NULL DEFAULT 'new'
              CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

INSERT INTO orders (customer_id, total) VALUES (1, 19.90)
RETURNING id, created_at;

• GENERATED ALWAYS AS IDENTITY is the modern replacement for the serial pseudo-type and avoids orphaned sequence ownership.
• Use numeric for money, never real or double precision — binary floating point cannot represent 0.10 exactly.
• Prefer timestamptz (stored as UTC) over timestamp so the session time zone cannot change what the value means.
• text has no performance penalty against varchar(n); add a CHECK constraint when a length limit is a real business rule.

Querying and shaping results

SELECT o.id,
       c.name,
       o.total,
       o.created_at AT TIME ZONE 'UTC' AS created_utc
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.status = ANY (ARRAY['paid', 'shipped'])
  AND  o.created_at >= now() - interval '30 days'
ORDER  BY o.created_at DESC
LIMIT  50;

-- aggregate, then filter the groups
SELECT status, count(*) AS n, sum(total) AS revenue
FROM   orders
GROUP  BY status
HAVING count(*) > 5
ORDER  BY revenue DESC;

• LIMIT and OFFSET work, but keyset pagination using WHERE (created_at, id) < (:last_created, :last_id) stays fast at any depth.
• ILIKE is a case-insensitive LIKE; a b-tree index does not serve it, so use citext or a functional index on lower(col) when you need speed.
• EXPLAIN (ANALYZE, BUFFERS) gives estimated versus actual rows plus cache behaviour — the first place to look when a query is slow.

FAQ

Related

JSONB and arrays Transactions and MVCC basics

Last refreshed 2026-09-18.