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.

load_things.sql

delete from things;
delete from categories;

--

with thing_groups as (
  select
    nextval('categories_id_seq'::regclass) as category_id,
    *
  from (
    select
      min(name) as category_name,
      array_agg(name order by name) as names
    from source_things
    group by
      case
        when type = 'vehicle' then
          substring(name, 1, 1)
        else
          name
      end
  ) s
),
categories as (
  insert into categories (id, name)
  select category_id, category_name
  from thing_groups
)
insert into things (name, category_id)
select unnest(names), category_id
from thing_groups;

--

select *
from categories c
inner join things t on t.category_id = c.id;

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 source_things (
  id serial PRIMARY KEY,
  type varchar,
  name varchar
);

INSERT INTO source_things (type, name)
VALUES
  ('fruit', 'apple'),
  ('fruit', 'cranberry'),
  ('vehicle', 'car'),
  ('vehicle', 'boat'),
  ('vehicle', 'truck'),
  ('vehicle', 'bicycle');

CREATE TABLE categories (
  id serial PRIMARY KEY,
  name varchar
);

CREATE TABLE things (
  id serial PRIMARY KEY,
  name varchar,
  category_id integer
);