| 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; |
| #!/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 "$@" |
| 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 | |
| ); | |