Tech Handbook Null Yard

SQL and PostgreSQL for Developers - Handbook

A relational database is more than a place to store rows. Data modeling, constraints, transactions, indexes and query execution are central to reliable systems. This handbook focuses on SQL and PostgreSQL from a developer's perspective.

As of September 2026, PostgreSQL 18 is the latest stable major line while PostgreSQL 19 is still in beta. The examples below intentionally focus on mechanisms that do not depend on one specific release.

Related topics: APIs and System Integrations, Go - Reading Code, Node.js, Python and Software Testing.

1. Relational model

A relational database stores data in tables.

Core concepts:

  • rows,
  • columns,
  • primary keys,
  • foreign keys,
  • constraints,
  • indexes,
  • relations between tables.

2. PostgreSQL

PostgreSQL is an open-source relational database known for reliability, standards support and advanced features.

It is widely used for web applications and backend systems.

3. Database and user

Create database:

CREATE DATABASE appdb;

Create user:

CREATE USER appuser WITH PASSWORD 'secret';

Grant access:

GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

In production, use stronger role separation and secret handling.

4. Table

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

5. INSERT

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice');

Return inserted row:

INSERT INTO users (email, name)
VALUES ('bob@example.com', 'Bob')
RETURNING *;

6. SELECT

SELECT * FROM users;

Selected columns:

SELECT id, email, name
FROM users
WHERE active = TRUE;

7. ORDER BY and LIMIT

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 20;

8. UPDATE

UPDATE users
SET active = FALSE
WHERE id = 10;

Always check the WHERE clause before running destructive updates.

9. DELETE

DELETE FROM users
WHERE id = 10;

Without WHERE, every row is affected.

10. Foreign key

Example:

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    title TEXT NOT NULL
);

The foreign key ensures the referenced user exists.

11. JOIN

SELECT
    posts.id,
    posts.title,
    users.email
FROM posts
JOIN users ON users.id = posts.user_id;

Common join types:

  • INNER JOIN,
  • LEFT JOIN,
  • RIGHT JOIN,
  • FULL JOIN.

12. GROUP BY

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id;

13. NULL

NULL means missing/unknown value.

Correct comparison:

WHERE deleted_at IS NULL

Not:

WHERE deleted_at = NULL

14. Constraints

Common constraints:

  • PRIMARY KEY,
  • FOREIGN KEY,
  • UNIQUE,
  • NOT NULL,
  • CHECK.

Example:

CHECK (price >= 0)

15. Indexes

Create:

CREATE INDEX idx_users_created_at
ON users(created_at);

Indexes can accelerate reads but increase storage and write overhead.

Do not add indexes blindly.

16. EXPLAIN

Inspect query plan:

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

With execution statistics:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

ANALYZE actually executes the query.

17. Transactions

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

Rollback:

ROLLBACK;

18. ACID - short version

Atomicity: all or nothing.

Consistency: constraints/invariants remain valid.

Isolation: concurrent transactions do not corrupt each other.

Durability: committed changes survive failures according to database guarantees.

19. Upsert

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

20. JSONB

PostgreSQL can store structured JSON efficiently:

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

Query:

SELECT payload->>'type'
FROM events;

Use relational columns for strongly structured frequently queried data, and JSONB when flexible structure is genuinely useful.

21. Backup

Logical dump:

pg_dump appdb > appdb.sql

Restore:

psql appdb < appdb.sql

Custom format:

pg_dump -Fc appdb > appdb.dump
pg_restore -d appdb appdb.dump

Test restores.

22. psql basics

Connect:

psql -h localhost -U appuser -d appdb

Useful meta-commands:

\l      databases
\c DB   connect
\dt     tables
\d NAME describe table
\du     roles
\q      quit

23. Connection string

Example:

postgresql://appuser:password@localhost:5432/appdb

Do not commit real passwords in source code.

24. Migrations

Database schema changes should be versioned.

Typical migration flow:

001_create_users.sql
002_add_posts.sql
003_add_index.sql

Use migration tools appropriate to the language/framework.

A migration should be repeatable, reviewable and safe for the target environment.

25. What you should know

You should understand:

  • tables,
  • primary/foreign keys,
  • INSERT/SELECT/UPDATE/DELETE,
  • JOIN,
  • GROUP BY,
  • NULL,
  • constraints,
  • indexes,
  • EXPLAIN,
  • transactions,
  • upsert,
  • JSONB,
  • backups,
  • psql,
  • migrations.

The most important rule: treat the database schema as code and review every destructive query before execution.

Official references

  • PostgreSQL documentation: https://www.postgresql.org/docs/current/
  • SQL commands: https://www.postgresql.org/docs/current/sql-commands.html
  • psql: https://www.postgresql.org/docs/current/app-psql.html
  • Backup and restore: https://www.postgresql.org/docs/current/backup.html
  • PostgreSQL release news: https://www.postgresql.org/about/news/