Dump PostgreSQL table with JSONB column as TSV file, pasteable into Google Sheets

README.md

This script is one I wrote to dump a PostgreSQL table with a JSONB column into TSV put on your pasteboard (on macOS) such that you can just paste it into a Google Sheet.

I needed this for a client and it was a bit of work, so I thought it might be useful to someone out there.

dump_postgresql_table_with_jsonb_column_as_tsv.sh

(
  echo $'Date\tName\tEmail\tPhone\tWhatever\tStuff 1\tStuff 2\tStuff 3';
  echo "
    SELECT array_to_json(array_agg(row_to_json(r)))
    FROM (
      SELECT json, created_at::timestamptz AT TIME ZONE 'CEST' AS created_at
      FROM some_table WHERE something = 'whatever' ORDER BY id DESC
    ) r;
  " |
  psql --tuples-only $(heroku config:get DATABASE_URL -a someapp) |
  jq -r '.[] | [
      ((.created_at | split(".") | .[0] | strptime("%Y-%m-%dT%H:%M:%S") | mktime) | strftime("%Y-%m-%d %H:%M:%S")),
      (.json.first_name + " " + .json.last_name),
      .json.email,
      .json.phone,
      .json.whatever,
      .json.stuff[]
    ] | @tsv
  ' |
  awk -v FS=$'\t' '$5 /^(whatever|something else|another thing)$/'
) | pbcopy