#PostgreSQL

13 snippets

• SQL

  Top N rows per group

  Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.

• SQL

  Soft delete with a partial unique index

  Keep deleted rows but still enforce unique emails among active rows using a partial unique index.

• SQL

  Walk a tree with a recursive CTE

  Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.

• SQL

  Pivot rows into columns with FILTER

  Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.

• SQL

  Query JSONB and index it with GIN

  Filter on JSONB fields with the containment operator and make it fast with a GIN index.

• SQL

  Fill in missing dates with generate_series

  Produce a row for every day in a range and left join your data so days with no activity show zero.

• SQL

  Automatically maintain an updated_at column

  A reusable PostgreSQL trigger function that sets updated_at to now() on every UPDATE.

• SQL Tested

  Upsert with INSERT ... ON CONFLICT (PostgreSQL)

  Insert a row or update it if the unique key already exists, atomically and safely under concurrency.

• SQL Tested

  Running total with a window function

  Compute cumulative sums per group without self-joins using SUM() OVER (PARTITION BY ... ORDER BY ...).

• PHP

  Query a database with PDO prepared statements

  Connect with PDO in exception mode and bind parameters to prevent SQL injection.

• SQL Tested

  Keyset (cursor) pagination instead of OFFSET

  Page through large tables quickly and stably by filtering on the last seen sort key.

• SQL Tested

  Find duplicate rows by one or more columns

  List values that appear more than once with GROUP BY and HAVING, including how many times.