Postgres PostGIS CheatSheet v2
This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.
How do I know the version of Postgis?
SELECT POSTGIS_FULL_VERSION();
How do I Show all databases?
1. Using “psql -l”
2. Using
postgres=# \l List of databases Name | Owner | Encoding ------------------+----------+---------- postgis | postgres | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 template_postgis | postgres | UTF8 (5 rows)
Note: Do not drop template databases if not necessary.
How do I run a script from the prompt?
psql -d cybersoftbj -u user -f myfile.sql
Its very useful in reloading user-defined functions.
How do I create a user/role?
CREATE ROLE lbs WITH LOGIN PASSWORD 'mypassword' SUPERUSER INHERIT CREATEDB CREATEROLE;
How do I change the password for a user/role?
ALTER ROLE lbs PASSWORD 'mynewpassword';
How to provide/restrict access privileges to tables?
GRANT SELECT ON TABLE TABLE TO user; REVOKE SELECT ON TABLE TABLE FROM user;
How to dump database in a text file?
pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql
How to dump database cleanly?
% pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql
How to rename a database?
ALTER DATABASE beijing_app RENAME TO beijing_app_20080801;
How to update using two tables?
UPDATE road_for_update u SET the_geom = r.the_geom FROM roads r WHERE r.rd_id = u.rd_id;
DROP TABLE
DROP TABLE IF EXISTS "my_table";
How to change a column type with Cast?
ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;
How to add a geometry column to a table?
SELECT AddGeometryColumn('public', 'poi', 'the_geom', 4326, 'POINT', 2)
Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1;Setting kernel shmmax for postgres
% sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg
11. How to backup table(s) from pg_dump?
% pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql
Change integer primary key to serial
CREATE SEQUENCE seq_job_id INCREMENT 1 MINVALUE 1000 MAXVALUE 2147483648 START 1000 CACHE 1; ALTER TABLE job ALTER COLUMN job_id SET DEFAULT NEXTVAL('seq_job_id'::regclass); SELECT * FROM job SELECT NEXTVAL('seq_job_id')
Date and Time Function Helper: date_add
CREATE OR REPLACE FUNCTION date_add(diffType Character Varying(15), incrementValue bigint, inputDateTime timestamp without time zone) RETURNS timestamp AS $$ DECLARE YEAR_CONST Char(15) := 'year'; MONTH_CONST Char(15) := 'month'; DAY_CONST Char(15) := 'day'; HOUR_CONST Char(15) := 'hour'; MIN_CONST Char(15) := 'minute'; SEC_CONST Char(15) := 'second'; dateTemp timestamp without time zone; intervals interval; BEGIN IF lower($1) = lower(YEAR_CONST) THEN SELECT cast(cast(incrementvalue AS character varying) || ' year' AS interval) INTO intervals; ELSEIF lower($1) = lower(MONTH_CONST) THEN SELECT cast(cast(incrementvalue AS character varying) || ' months' AS interval) INTO intervals; ELSEIF lower($1) = lower(DAY_CONST) THEN SELECT cast(cast(incrementvalue AS character varying) || ' day' AS interval) INTO intervals; ELSEIF lower($1) = lower(HOUR_CONST) THEN SELECT cast(cast(incrementvalue AS character varying) || ' hours' AS interval) INTO intervals; ELSEIF lower($1) = lower(MIN_CONST) THEN SELECT cast(cast(incrementvalue AS character varying) || ' minutes' AS interval) INTO intervals; ELSEIF lower($1) = lower(SEC_CONST) THEN SELECT cast(cast(incrementvalue AS character varying) || ' seconds' AS interval) INTO intervals; END IF; dateTemp:= inputDateTime + intervals; RETURN dateTemp; END; $$ LANGUAGE plpgsql;
How to set the current timezone in postgres?
# Session based only SET time zone 'utc'; SELECT current_setting('TIMEZONE'); # Permanent # Edit /usr/local/var/postgres/postgresql.conf (#postgres installed via homebrew) timezone = 'UTC'
Date/Time Functions
SELECT current_setting('TIMEZONE'); --"Australia/Victoria" SELECT Now(), timezone('UTC', now()), EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)), to_timestamp(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0))), to_timestamp(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)))::TIMESTAMP --"2011-11-14 09:29:14.249427+11" --"2011-11-13 22:29:14.249427" --1321223354 --"2011-11-14 09:29:14+11" --"2011-11-14 09:29:14"
How to specify the id of a sequence to prevent SQL Error: PGRES_FATAL_ERROR:ERROR: duplicate key value violates unique constraint “gps_histories_pkey”?
This happens when the maximum number of records in gps_histories is not in sync with the sequence id.
SELECT SETVAL('dfms_4000.gps_histories_id_seq', (SELECT MAX(id) FROM dfms_4000.gps_histories)+1)