Skip to content

Instantly share code, notes, and snippets.

View rustprooflabs's full-sized avatar

Ryan Lambert rustprooflabs

View GitHub Profile
@rustprooflabs
rustprooflabs / db.py
Created October 29, 2019 17:06
Python and Postgres - psycopg2 quick example
import getpass
import psycopg2
import psycopg2.extras
def select_one(sql_raw, params):
""" Runs SELECT query that will return zero or 1 rows. `params` is required."""
return _execute_query(sql_raw, params, 'sel_single')
@rustprooflabs
rustprooflabs / srid_units.sql
Last active May 18, 2021 17:32
Creates view in PostGIS enabled database to make it easier to find what units each SRID is in.
CREATE OR REPLACE VIEW public.srid_units AS
SELECT srid, CASE WHEN proj4text LIKE '%+units=%' THEN True
ELSE False
END AS units_set,
CASE WHEN proj4text LIKE '%+units=m%' THEN 'Meters'
WHEN proj4text LIKE '%+units=ft%' THEN 'Feet'
WHEN proj4text LIKE '%+units=us-ft%' THEN 'Feet'
WHEN proj4text LIKE '%+units=link%'
OR proj4text LIKE '%+units=%'
THEN 'Set, not caught properly'
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE SERVER fdw_files FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE public.fdw_us_state_bbox
(
id INT,
fips TEXT,
@rustprooflabs
rustprooflabs / example-partition-pruning-not-happening.sql
Created February 24, 2021 14:48
Minimal test case illustrating example where partition pruning is not happening. Simplified example to better test findings for Query 2 on this post https://blog.rustprooflabs.com/2021/02/postgres-partition-openstreetmap-road-v1-review
DROP TABLE IF EXISTS p_group;
CREATE TABLE p_group
(
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
osm_date DATE NOT NULL,
region TEXT NOT NULL,
CONSTRAINT uq_p_group UNIQUE (osm_date, region)
);
INSERT INTO p_group (osm_date, region)