Skip to content

Instantly share code, notes, and snippets.

@smintz
Last active November 11, 2020 08:27
Show Gist options
  • Save smintz/6508354 to your computer and use it in GitHub Desktop.
Save smintz/6508354 to your computer and use it in GitHub Desktop.
#!/bin/bash
SOURCEINSTANCE=${SOURCEINSTANCE:-''}
SOURCEUSER=${SOURCEUSER:-'admin'}
SOURCEPORT=${SOURCEPORT:-'5439'}
SOURCEDB=${SOURCEDB:-'db'}
SOURCESCHEMA=${SOURCESCHEMA:-'public'}
SCHEMA=${SCHEMA:-'public'}
echo "CREATE TABLE ${SCHEMA}.${TABLE} ("
psql -h ${SOURCEINSTANCE} -U ${SOURCEUSER} -p ${SOURCEPORT} ${SOURCEDB} -t -c "select (\"column\" || ' ' || type || ' ENCODE ' || encoding || ',' ) from pg_table_def where schemaname='$SCHEMA' and tablename = '$TABLE'" | sed 's/ENCODE none/ENCODE RAW/' | sed '$d' | sed '$ s/,$//'
echo ")"
SORTKEY=$(psql -h ${SOURCEINSTANCE} -U ${SOURCEUSER} -p ${SOURCEPORT} ${SOURCEDB} -t -c "select \"column\" from pg_table_def where schemaname='$SCHEMA' and tablename = '$TABLE' and sortkey > 0 order by sortkey" | tr "\n" "," | sed 's/\([,]*\)$//')
[ -n "$SORTKEY" ] && echo "sortkey ($SORTKEY)"
DESTKEY=$(psql -h ${SOURCEINSTANCE} -U ${SOURCEUSER} -p ${SOURCEPORT} ${SOURCEDB} -t -c "select \"column\" from pg_table_def where schemaname='$SCHEMA' and tablename = '$TABLE' and distkey = true" | tr "\n" "," | sed 's/\([,]*\)$//')
[ -n "$DESTKEY" ] && echo "distkey ($DESTKEY)"
echo ";"
@ofirnn
Copy link

ofirnn commented Nov 12, 2015

TNX!

@pkallos
Copy link

pkallos commented Jan 7, 2016

👍 u da best thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment