Skip to content

Instantly share code, notes, and snippets.

@plindberg
Last active June 27, 2018 15:05
Show Gist options
  • Save plindberg/bf8574265fcc7e12a5a1bd3cbdcc8fce to your computer and use it in GitHub Desktop.
Save plindberg/bf8574265fcc7e12a5a1bd3cbdcc8fce to your computer and use it in GitHub Desktop.
Dump PostgreSQL table with JSONB column as TSV file, pasteable into Google Sheets

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.

(
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment