Skip to content

Instantly share code, notes, and snippets.

@dsaiztc
dsaiztc / postgres-cheatsheet.md
Created May 3, 2020 — forked from Kartones/postgres-cheatsheet.md
PostgreSQL command line cheatsheet
View postgres-cheatsheet.md

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)
@dsaiztc
dsaiztc / gzip_str.py
Created Mar 13, 2020 — forked from Garrett-R/gzip_str.py
Demo of how to gzip and gunzip a string in Python 3
View gzip_str.py
import gzip
import io
def gzip_str(string_):
out = io.BytesIO()
with gzip.GzipFile(fileobj=out, mode='w') as fo:
fo.write(string_.encode())
bytes_obj = out.getvalue()
@dsaiztc
dsaiztc / queries.sql
Last active Jul 2, 2019 — forked from iconara/queries.sql
Low level Redshift cheat sheet
View queries.sql
-- Table information like sortkeys, unsorted percentage
-- see http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html
SELECT * FROM svv_table_info;
-- Table sizes in GB
SELECT t.name, COUNT(tbl) / 1000.0 AS gb
FROM (
SELECT DISTINCT datname, id, name
FROM stv_tbl_perm
JOIN pg_database ON pg_database.oid = db_id
View redshift_table_change
START TRANSACTION ;
ALTER TABLE schema_name.table_name RENAME TO table_name_old;
CREATE TABLE schema_name.table_name_new
-- Creation statement
;
INSERT INTO schema_name.table_name_new (
SELECT
View barcelona.geojson
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
View pipenv_github_cython.sh
pipenv install cython
pipenv install git+https://github.com/jswhit/pyproj.git#egg=pyproj
View kernel_virtualenv.md

One thing you can do from the command line is activate the virtual environment then install the kernel specification from within the environment:

python -m ipykernel install --user --name some-env --display-name "Python (some-env)"
ipython kernelspec list
View scp-cheatsheet.md

Basic Syntax

$ scp source_file_path destination_file_path

Uploading

Single file

View ip_address.py
import ipaddress
for host in ipaddress.ip_network('204.93.240.0/24').hosts():
print(host)
View redshift_disk_usage.sql
SELECT TRIM(pgn.nspname) AS SCHEMA,
TRIM(a.name) AS TABLE,
id AS TableId,
decode(pgc.reldiststyle,
0, 'EVEN',
1,det.distkey ,
8,'ALL'
) AS DistKey,
decode(pgc.reldiststyle,
8,NULL,