SQL and PostgreSQL, step by step

1. Stage 1

   Query basics

   Select, filter and group, checked as you go.

   1. 1

      SQL exercise

      SELECT, WHERE and ORDER BY

      Practice SELECT, WHERE, ORDER BY, LIMIT and DISTINCT on a real database in your browser, with instant checking of your results, on PostgreSQL or SQLite.

   2. 2

      SQL exercise

      Filtering: AND, IN, LIKE and NULL

      Practice filtering rows in SQL with AND, IN, LIKE, BETWEEN and IS NULL on a real database in your browser, and see why = NULL never matches, on PostgreSQL or SQLite.

   3. 3

      SQL exercise

      Counting and grouping: GROUP BY and HAVING

      Practice COUNT, SUM, AVG, GROUP BY and HAVING on a real database in your browser, with instant checks, and learn why a column must appear in GROUP BY.

   4. 4

      Tool

      SQL Playground

      Try your own queries on the same data.

2. Stage 2

   Joins, subqueries and changing data

   Combine tables, write the harder queries, and the errors they bring.

   1. 5

      SQL exercise

      Joining tables: INNER, LEFT and self joins

      Practice SQL joins on a real database in your browser: INNER JOIN, LEFT JOIN, finding rows with no match, and joining a table to itself, on PostgreSQL or SQLite.

   2. 6

      SQL exercise

      Subqueries, CTEs and window functions

      Practice subqueries, WITH clauses, CASE and window functions like ROW_NUMBER on a real database in your browser, using a small shop's orders, on PostgreSQL or SQLite.

   3. 7

      SQL exercise

      Changing data: INSERT, UPDATE, DELETE and transactions

      Practice INSERT, UPDATE, DELETE and transactions safely, on a real database in your browser: see a foreign key refuse you, and undo a mistake with ROLLBACK.

   4. 8

      Article

      PostgreSQL "column must appear in the GROUP BY clause or be used in an aggregate function"

      Fix the GROUP BY error in PostgreSQL: understand why it happens, then aggregate the column, group by it or by the primary key, or pick one row per group with DISTINCT ON.

   5. 9

      Article

      PostgreSQL "relation does not exist": when the table is right there

      Fix PostgreSQL "ERROR: relation does not exist": check the schema and search_path, quoted names with capitals, which database you are in, and whether migrations have run.

   6. 10

      Article

      PostgreSQL "invalid input syntax for type integer": find the bad value and handle it

      Fix "invalid input syntax for type integer": find empty strings and text where a number is expected, convert with NULLIF and pg_input_is_valid, import clean data.

   7. 11

      Article

      PostgreSQL "duplicate key value violates unique constraint": causes and fixes

      Fix "duplicate key value violates unique constraint" in PostgreSQL: tell a real duplicate from a sequence that is out of sync, and fix both safely.

3. Stage 3

   Connecting and permissions

   Get in, with the right user, from the right place, with the right rights.

   1. 12

      Cheat sheet

      PostgreSQL Admin Cheat Sheet

      psql commands and queries for running PostgreSQL: roles and grants, sizes, active queries and locks, sequences, vacuum, config, backup and restore.

   2. 13

      Article

      PostgreSQL "Connection refused … Is the server running?": find out why nothing answers on 5432

      Fix PostgreSQL "connection refused": check that it runs, read why it did not start, then fix listen_addresses, the port, the firewall or Docker.

   3. 14

      Article

      PostgreSQL "FATAL: password authentication failed for user": find the real reason

      Fix PostgreSQL "password authentication failed" and "Peer authentication failed": read the server log, check pg_hba.conf, reset the password, and handle Docker.

   4. 15

      Article

      PostgreSQL "Peer authentication failed for user": connect the right way, or change pg_hba.conf

      Fix PostgreSQL "Peer authentication failed for user": use sudo -u postgres, connect over TCP with -h localhost, or change pg_hba.conf to scram-sha-256 and reload.

   5. 16

      Article

      PostgreSQL "no pg_hba.conf entry for host": allow this connection, the safe way

      Fix PostgreSQL "FATAL: no pg_hba.conf entry for host": read the host, user and SSL in the message, add a narrow rule to pg_hba.conf, reload it, and handle Docker and SSL.

   6. 17

      Article

      PostgreSQL "permission denied for table": causes and fixes

      Fix "ERROR: permission denied for table" in PostgreSQL: check what a role can do, grant the right privileges, and fix the schema, sequence and future-table cases.

4. Stage 4

   Under load

   Too many connections, and transactions that block each other.

   1. 18

      Article

      PostgreSQL "FATAL: sorry, too many clients already": find who holds the connections

      Fix PostgreSQL "FATAL: sorry, too many clients already": see who holds the connections, end the idle ones, size your pools, and add PgBouncer before raising the limit.

   2. 19

      Article

      PostgreSQL "deadlock detected": read the message, then lock in the same order

      Fix PostgreSQL "ERROR: deadlock detected": read which processes and rows were involved, lock rows in a consistent order, and retry on SQLSTATE 40P01.

   3. 20

      Guide

      Can't connect to the database? Find the cause, step by step

      PostgreSQL, MySQL, SQL Server, Redis, MongoDB or Oracle refusing the connection.

Your progress is saved in this browser only, never sent anywhere. Clearing the browser data clears it too.