Skip to content

Instantly share code, notes, and snippets.

@seamusabshere

seamusabshere/hcsv

Last active January 18, 2023 01:40
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save seamusabshere/6708941 to your computer and use it in GitHub Desktop.
Save seamusabshere/6708941 to your computer and use it in GitHub Desktop.
Download ZIP
hcsv - dump id + values from one hstore column
Raw
hcsv
#!/usr/bin/env ruby
# Usage: hcsv DBNAME TBLNAME HSTORECOL
# Output columns will be id + all the hstore keys
dbname, tblname, hstorecol = ARGV[0..2]
# Get hstore keys
out = `psql #{dbname} --tuples --command "SELECT DISTINCT k FROM (SELECT skeys(#{hstorecol}) AS k FROM #{tblname}) AS dt ORDER BY k"`
headers = out.split(/\n/).map(&:strip)
# Dump CSV of id + all hstore keys
hstore_headers_sql = headers.map { |k| %{#{hstorecol}->'#{k}' AS "#{k}"} }.join(', ')
system 'psql', dbname, '--tuples', '--command', "COPY (SELECT id, #{hstore_headers_sql} FROM #{tblname}) TO STDOUT (FORMAT 'csv', HEADER)"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment