Postgres Cheatsheet

Some time in the future I'll have to organize this better

postgresql install from here http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS22UbuntuPGSQL95Apt
quick set up to allow remote connections...THIS IS FOR TESTING ONLY
use netstat -nlt to check if port 5432 is open
update postgresql.conf nano /etc/postgresql/9.6/main/postgresql.conf
uncomment listen_addresses and change to listen_addresses = '*'
Make sure port is 5432, it looks like more recent installation use 5433 instead
update pga_hd.conf nano /etc/postgresql/9.6/main/pg_hba.conf
add lines host all all 0.0.0.0/0 md5 host all all ::/0 md5
- change the second column all to a specific user if you can to restrict access.
restart postgres service postgresql restart
double check netstat -nlt should look something like

Proto	Send-Q	Local Address	Foreign Address	State
tcp	0 0.0.0.0:22	0.0.0.0:*	LISTEN
tcp	0 0.0.0.0:5432	0.0.0.0:*	LISTEN
tcp6	0 :::22	:::*	LISTEN
tcp6	0	:::5432	:::*	LISTEN

vacuum alanyze

vacuumdb --analyze -h localhost -U user -d databasename

If Adding shapefile with Multiploygons use -nlt PROMOTE_TO_MULTI if you get error

"ERROR:Geometry type (MultiPolygon) does not match column type (Polygon)"
ogr2ogr -f "PostgreSQL" -t_srs EPSG:3435 -sql "SELECT name FROM layer" \
PG:"host=000.000.000.000 dbname='database' user='user' password='password'" \
-nln layername -lco SCHEMA=public -lco OVERWRITE=YES -lco GEOMETRY_NAME=geom filename.shp

pg_dump for backups

pg_dump -f '/home/projects/backup021916' -C --schema=schemaname -d database -U user

pg_dump --format=t -f '/home/projects/backups/backup100616.tar' -C --schema=quercus -d database -U user -h localhost

pg_dump to gzip and no ownership

pg_dump -d database -U user -h localhost --no-owner | gzip > /home/backups/filename.gz

restore database backup

psql -f '/home/projects/backup021916' -C --schema=schemaname -d database -U user

pg_restore -v -F t backup100616.tar -h localhost -U user -d database

copy table into new CSV

copy (select * from schema.table where labelname = 'Lake') to '/home/projects/lake.csv' (format csv, delimiter ',' , header);

import CSV insert into table. First create the table to import into, then...

copy schema.table_name
(table column1, table column2, etc) 
from
'/home/projects/data.csv'
with csv header;

List all of table a that intersects table b

select p.gid, p.name,c.district from public.working_poi as p join public.districts as c /
on st_intersects(p.geom, c.geom) order by p.name;

Update Geometry

SELECT UpdateGeometrySRID('roads','geom',4326);

Import SHP file with OGR and target projection

ogr2ogr -f "PostgreSQL" -t_srs EPSG:3435 -sql "SELECT DistrictName from Districts" /
PG:"dbname='mytable' user='username' password='userpassword'" -nln DistrictTableName /
-lco SCHEMA=public -lco OVERWRITE=YES -lco GEOMETRY_NAME=geom Districts.shp

Create topology Trails create topology

SELECT topology.CreateTopology('trails_topo', 3435);
SELECT topology.AddTopoGeometryColumn('trails_topo', 'public', 'temp_trails', 'topogeom', 'LINESTRING');
UPDATE public.temp_trails SET topogeom = toTopoGeom(geom, 'trails_topo', 1, 3);

Drop topology

SELECT topology.DropTopology('trails_topo');

Example of create a topological tabel joined back to attributes: *note currently there is no way to relate back linestrings to their original feature (other than a spatial selection)

create table
quercus.trails1_data_topo
as select t.new_pk, e.geom
from quercus.trails1 as t, trails1_topo.edge_data as e , trails1_topo.relation r
where
r.topogeo_id = t.new_pk
and
e.edge_id = r.element_id;

list all columns in a table

select column_name from information_schema.columns where table_name='info_history';

look up materialized view definitions

select * from pg_matviews WHERE matviewname = 'name_of_mat_view';

look up functions and triggers

select * from pg_trigger;

SELECT  p.proname
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      p.pronamespace = n.oid
WHERE   n.nspname = 'public' and p.proname like '%activities%' order by p.proname;

create table from another table with indexs and constraints included

create table schema.table2 (like schema.table1 including defaults including constraints including indexes );

create a domain

create domain schema.domain_name as text constraint trail_difficulty_constraint check( value in ('easy', 'intermediate',
'advanced', 'expert'));

comment on domain trail_difficulty_dom is 'Values can only be easy, intermediate, advanced, expert';

to edit domain you must first drop the check constraint by name \dD schema.domain_name will not give you the name of the check constraint. To find it, you can use this.

select conname from pg_constraint where contypid = 'domain_name'::regtype;

alter domain domain_name drop constraint trail_difficulty_constraint;

alter domain domain_name add constraint new_trail_constraint check (value in ('foot', 'single track', 'natural surface',
'paved' , 'primitive', 'stone', 'unpaved'));

update table based on separate TABLE

UPDATE b
SET column1 = a.column1,
  column2 = a.column2,
  column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1

update table testb with testa dynamically (same IDs and columns)

DO
$do$
BEGIN
EXECUTE (

SELECT
'UPDATE testb
SET (' || string_agg(quote_ident(column_name), ',') || ') = (' || string_agg('testa.' || quote_ident(column_name), ',') || ')
FROM testa
where testa.id = testb.id'

FROM information_schema.columns
WHERE table_name   = 'testa'       -- table name, case sensitive
AND table_schema = 'public'  -- schema name, case sensitive
AND column_name <> 'id'      -- all columns except id);
END
$do$;

Trails create topology

SELECT topology.CreateTopology('trails_topo', 3435);
SELECT topology.AddTopoGeometryColumn('trails_topo', 'public', 'temp_trails', 'topogeom', 'LINESTRING');
UPDATE public.temp_trails SET topogeom = toTopoGeom(geom, 'trails_topo', 1, 3);

To drop topology

SELECT topology.DropTopology('trails_topo');

Distinct on (postgresonline.com)

SELECT DISTINCT ON (c.customer_id) 
c.customer_id, c.customer_name, o.order_date, o.order_amount, o.order_id 
FROM customers c LEFT JOIN orders O ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date DESC, o.order_id DESC;

You can have search_path set at the following levels and this is the order
At the function level - only lasts for life of execution of function within function:

ALTER FUNCTION some_func() SET search_path=public,utility;

At the session level - only lasts for the life of the session:

set search_path=public,utility;

At the database user level - only takes affect for new sessions:

ALTER ROLE postgres IN DATABASE mydb SET search_path = public, utility;

At the server user level -- only takes affect for new sessions:

ALTER ROLE postgres SET search_path = public,utility;

At the database level -- only takes affect for new sessions:

ALTER DATABASE mydb SET search_path = public, utility;

(replace text within a field) [http://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field]

update names set name= regexp_replace(name, 'Old Text', 'New Text');
remove newline characters from column

UPDATE schema.table
SET maintenance_div =
regexp_replace(maintenance_div, E'[\\n\\r]+', ' ', 'g' );

st_intersect - create a new table where 2 tables intersect

create table public.temp_trail_1 as select ST_Intersection(t.geom, d.geom) as geom,t.trails_id
from public.temp_trails as t, public.divisions as d where ST_Intersects(t.geom,d.geom) = TRUE;

remove holes from polygons

create table public.boundary_fix as SELECT s.gid, ST_Collect(ST_MakePolygon(s.geom)) As geom
FROM (SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
FROM public.boundary )  As s
GROUP BY gid;

until I find the proper way to do this, this is how im counting the points that make up a polygon

select st_dumppoints(geom) from public.boundary_fix;

Select features by bounding box

select id, the_geom from your_table as n
where
n.the_geom && st_makeenvelope(minLon, minLat, maxLon, maxLat, 4326)

glw/postgres-postgis-cheat-sheet.md

Postgres Cheatsheet