How this PostgreSQL track works
- Read-focused — no in-browser SQL lab yet — copy SQL into psql, a local PostgreSQL server, or DB Fiddle (PostgreSQL dialect). The track uses
execution_profile: sql_sandbox; the playground is not wired yet. - Postgres-specific — JSONB, arrays, RETURNING, extensions, vacuum, EXPLAIN ANALYZE, and ops patterns after portable SQL on SQL.
- Pair with — Django and Python for app integration; compare MySQL when your stack differs.
- Prerequisites — complete Introduction to SQL first (SELECT, JOINs, basic DDL).
Practice on databases you create. Never run destructive SQL on production clusters.
Install on your device (macOS, Linux, Windows)
Install PostgreSQL 15+ and psql for local databases.
macOS
brew install postgresql@16andbrew services start postgresql@16
Linux
- Debian/Ubuntu:
sudo apt install -y postgresql postgresql-contrib - Fedora:
sudo dnf install -y postgresql-serverthen initialize per distro docs.
Windows
- Installer from postgresql.org/download/windows or
winget install PostgreSQL.PostgreSQL
Verify: psql --version and psql -U postgres -c "SELECT version();"
PostgreSQL (often called Postgres) is an open-source relational database known for standards compliance, rich data types (JSONB, arrays, UUID), and extension ecosystem (PostGIS, pgvector). This track builds on portable SQL from the core SQL track and focuses on Postgres-specific features you will use with Django, Python, and modern data stacks.
Prerequisites and how this track works
Complete Introduction to SQL first—you should be comfortable with SELECT, JOINs, and basic DDL. This track is read-focused (execution_profile: sql_sandbox): copy examples into psql, local Postgres, or DB Fiddle with the PostgreSQL dialect.
Lessons highlight what differs from MySQL and ANSI SQLite—see MySQL when your stack uses that engine instead.
What you will learn
psqlworkflow, SERIAL/UUID keys, RETURNING, arrays, JSONB- Schema: constraints, indexes, partial indexes, schemas, extensions
- Queries: window functions, CTEs, full-text search basics
- Operations: vacuum, EXPLAIN ANALYZE, backups, pooling, Django/Python integration
Connect and verify Postgres
-- In psql or any Postgres client:
SELECT version();Practice: Copy SQL into psql, a local PostgreSQL server, or DB Fiddle (PostgreSQL dialect). Compare output with the lesson.
version() confirms you are on PostgreSQL and shows the server build—useful when tutorials assume a specific major version.
Safety note
Practice on databases you create (CREATE DATABASE practice;). Never run destructive DDL on shared production clusters. Use roles with least privilege outside learning sandboxes.
Important interview questions and answers
- Q: Why learn PostgreSQL after SQL?
A: Core SQL transfers; this track teaches engine-specific types, DDL, and ops that interviews and Django deployments expect. - Q: What is JSONB?
A: Binary JSON storage with indexing—Postgres-native alternative to document stores for semi-structured fields.
Self-check
- What track should you finish before starting here?
- What does SELECT version() tell you?
Challenge
Connect with psql and verify Postgres
- Install PostgreSQL locally or use DB Fiddle (PostgreSQL).
- Connect with
psql(or the fiddle SQL pane). - Run
SELECT version();and read the server version string.
Done when: you see a version row identifying PostgreSQL.
Tip: Finish the psql SELECT version(); challenge before moving on—later lessons assume you can connect.
Interview prep
- Prerequisite track?
Complete the core SQL track (/sql/intro) before Postgres-specific features.
- Why read-focused?
In-browser sql_sandbox lab is not wired; practice in psql, local Postgres, or DB Fiddle.