PostgreSQL Basics

Installation and psql

Install PostgreSQL from official packages or use managed services (RDS, Cloud SQL, Supabase, Neon). psql is the interactive terminal client for running SQL and inspecting schema.

Initialize with createdb and createuser or use SQL CREATE DATABASE. PostgreSQL listens on port 5432 by default; configure pg_hba.conf for authentication methods.

• Use TIMESTAMPTZ for timestamps that respect time zones
• BIGSERIAL auto-increments 64-bit integers for primary keys
• Connect with connection URI: postgresql://user:pass@host:5432/myapp

createdb myapp
psql myapp

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Databases and Schemas

A PostgreSQL cluster contains databases isolated from each other. Within a database, schemas namespace objects—public is default. Use schemas to organize multi-tenant or modular applications.

Search path determines unqualified name resolution. Set search_path per role or session for cleaner SQL.

CREATE SCHEMA billing;
CREATE TABLE billing.invoices (
  id BIGSERIAL PRIMARY KEY,
  amount NUMERIC(12,2) NOT NULL
);

Core Data Types

PostgreSQL offers rich types: INTEGER, BIGINT, NUMERIC, TEXT, BOOLEAN, DATE, TIMESTAMP, TIMESTAMPTZ, UUID, JSONB, ARRAY, and more. Choose NUMERIC for exact decimal arithmetic.

TEXT has no length limit unlike VARCHAR(n)—use CHECK constraints if length limits matter. UUID primary keys avoid coordination in distributed ID generation.

• JSONB stores binary JSON optimized for indexing and containment queries
• Arrays support multi-valued columns without junction tables for simple cases
• Use ENUM types sparingly—ALTER TYPE adds values but removing is hard

Basic Queries

INSERT, SELECT, UPDATE, DELETE follow SQL standards. RETURNING clause returns affected rows—convenient for getting generated IDs without a second query.

Use parameterized queries ($1, $2 in libpq) from applications to prevent SQL injection.

INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada Lovelace')
RETURNING id, created_at;

SELECT id, email FROM users WHERE name ILIKE 'ada%';

Getting Help

\d table_name in psql describes columns and indexes. EXPLAIN shows query plans. PostgreSQL documentation is comprehensive—bookmark it for function and operator reference.

Enable log_statement or pg_stat_statements in development to learn what your ORM actually executes.

\d users
EXPLAIN SELECT * FROM users WHERE email = 'ada@example.com';