PostgreSQL Basics

10 examples to get you started with PostgreSQL fundamentals - 7 basic and 3 intermediate.

Prerequisites

Install PostgreSQL 18 locally or use Docker. You need psql and superuser access to create sample objects.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. Connect to a Database

Every session attaches to exactly one database on the cluster.

\c postgres
SELECT current_database(), current_user, version();

• The cluster is the running PostgreSQL instance; database is the connection target
• \c dbname in psql switches databases without reconnecting the client binary
• version() returns the server build; pin client tools to the same major when possible

Related: Postgres Architecture - processes behind the connection

2. Create a Schema and Table

Schemas group tables, views, and functions. public is the default schema.

CREATE SCHEMA app AUTHORIZATION postgres;
 
CREATE TABLE app.customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO app.customers (email) VALUES ('ada@example.com');

• Use schemas to separate apps or tenants in one database
• Prefer identity columns over serial for new tables (PostgreSQL 10+)
• Constraints live in the catalog and survive application redeploys

3. Query with Explicit Columns

Readable SQL lists columns and filters early.

SELECT id, email, created_at
FROM app.customers
WHERE email LIKE '%@example.com'
ORDER BY created_at DESC
LIMIT 10;

• Avoid SELECT * in application code; schema changes break clients silently
• Filter on indexed columns when tables grow
• LIMIT without ORDER BY returns an arbitrary row set

Related: SQL Querying Basics

4. Inspect Object Metadata

System catalogs describe every object in the cluster.

SELECT n.nspname AS schema, c.relname AS table_name, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app' AND c.relkind = 'r'
ORDER BY 1, 2;

• pg_class + pg_namespace are the low-level table registry
• information_schema offers SQL-standard views with less detail
• Metadata queries power migrations, linters, and data catalogs

Related: System Catalogs

5. Use Transactions for Multi-Step Writes

ACID transactions group changes into one atomic unit.

BEGIN;
INSERT INTO app.customers (email) VALUES ('grace@example.com');
UPDATE app.customers SET email = lower(email) WHERE email = 'grace@example.com';
COMMIT;

• Default autocommit wraps each statement unless you BEGIN
• On error, ROLLBACK undoes work in the current transaction
• Keep transactions short to reduce lock and bloat pressure

Related: Transactions Basics

6. Add an Index for Lookup Patterns

B-tree indexes accelerate equality and range predicates on columns.

CREATE INDEX customers_created_at_idx
  ON app.customers (created_at DESC);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM app.customers
WHERE created_at > now() - interval '7 days';

• Indexes speed reads but add write amplification on INSERT/UPDATE
• EXPLAIN ANALYZE executes the query and shows actual timings
• Index only what you query; verify plans after schema changes

Related: Indexes Basics

7. Install an Extension

Extensions ship optional features as controlled modules.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
SELECT gen_random_uuid();

• Extensions are versioned and recorded in pg_extension
• Some extensions require superuser or CREATE on the database
• Pin extension versions in migration scripts for reproducibility

Intermediate Examples

8. Set search_path Safely

Unqualified names resolve via search_path. Misconfiguration causes wrong-table bugs.

SHOW search_path;
 
SET search_path = app, public;
 
SELECT email FROM customers LIMIT 1;
 
-- Safer in apps: always schema-qualify
SELECT email FROM app.customers LIMIT 1;

• Default is "$user", public - empty schemas can surprise new roles
• Security-sensitive code should schema-qualify or set path per session
• Never rely on public for multi-tenant data without RLS

Related: Database, Schema & Search Path

9. Check Server Health Signals

Quick catalog queries expose connections, bloat hints, and version.

SELECT count(*) AS backends FROM pg_stat_activity;
 
SELECT datname, numbackends, xact_commit, blks_read
FROM pg_stat_database
WHERE datname = current_database();

• pg_stat_activity lists live sessions and wait events
• pg_stat_database aggregates commits, reads, and conflicts per database
• Pair catalog checks with OS metrics (CPU, disk, memory) for triage

Related: Postgres Architecture

10. Choose timestamptz for Events

Store instants in UTC; convert at the application edge.

ALTER TABLE app.customers
  ADD COLUMN last_login_at timestamptz;
 
UPDATE app.customers
SET last_login_at = now()
WHERE email = 'ada@example.com';
 
SELECT email, last_login_at AT TIME ZONE 'America/New_York' AS login_ny
FROM app.customers;

• timestamptz stores UTC internally; display depends on session TimeZone
• Avoid timestamp without time zone for real-world event times
• Document timezone assumptions in API contracts

Related: Date, Time & Timestamptz

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.