README.md

PostgreSQL Fiddle

Requires bash and docker.

Usage

./pg-fiddle start to start a docker container running a PostgreSQL server

While the server is running:

• ./pg-fiddle schema to reset the schema of the database from schema.sql.
• ./pg-fiddle query <query-file> to run queries on the database.

pg-fiddle

#!/usr/bin/env bash

# Subcommand to start the postgres server
pgf-start() {
  docker run --name pg-fiddle --rm -it -v "$PWD:/pg-fiddle" postgres:11.5
}

# Subcommand to reset the schema
pgf-schema() {
  docker exec -i pg-fiddle psql -U postgres < schema.sql
}

# Subcommand to run one or more queries and show results
pgf-query() {
  for f in "$@"; do
    echo -e "\e[4;1m$f\e[0m\n"

    docker exec -i pg-fiddle psql -U postgres -d pg_fiddle < "$f" | sed -e 's/^/  /'
  done
}

# Take the first arg as the subcommand to run
subcommand=$1
shift

# Pass the rest of the args to the subcommand
pgf-$subcommand "$@"

schema.sql

DROP DATABASE IF EXISTS pg_fiddle;

CREATE DATABASE pg_fiddle;

\c pg_fiddle;

-- Add your tables and data below

CREATE TABLE things (
  id integer PRIMARY KEY,
  -- A = B + C
  A decimal,
  B decimal,

  -- C = D - E
  C decimal,
  D decimal,

  -- E = F * B
  E decimal,
  F decimal
);

INSERT INTO things VALUES
  -- solve for A using B and C
  (1, NULL, 4, 6),

  -- solve for B using A and C
  (2, 10, NULL, 6),

  -- solve for C using A and B
  (3, 10, 4, NULL),

  -- can't solve for B or C
  (4, 10, NULL, NULL);

INSERT INTO things VALUES
  -- solve for C using D and E
  -- then solve for B using A and C
  -- then solve for F using E and B
  (5, NULL, 4, NULL, 8, 2, NULL),
  -- solve for E using F and B
  -- then solve for C using D and E
  -- then solve for A using B and C
  (6, NULL, 10, NULL, 8, NULL, 0.5);

solve.sql

with recursive derived_things as (
  select id, a, b, c, d, e, f from things
  union
  select
    id,
    coalesce(a, b + c) a,
    coalesce(b, a - c, e / f) b,
    coalesce(c, a - b, d - e) c,
    coalesce(d, c + e) d,
    coalesce(e, d - c, f * b) e,
    coalesce(f, e / b) f
  from derived_things
)
select distinct on (id) *
from derived_things
order by id, array[a, b, c, d, e, f]