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? **

Using “psql -l”
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 describe a table?
\d schema_name.table_name or \d table_name (which references public)

test_db=# \d dfms_4000.drivers
                                         Table "dfms_4000.drivers"
   Column   |            Type             |                           Modifiers                            
------------+-----------------------------+----------------------------------------------------------------
 id         | integer                     | not null default nextval('dfms_4000.drivers_id_seq'::regclass)
 fleet_id   | smallint                    | not null default 
 name       | character varying(32)       | default ''::character varying
 tag_id     | character varying(32)       | default ''::character varying
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
 is_asset   | boolean                     | default false
 pin        | integer                     | 
Indexes:
    "drivers_pkey" PRIMARY KEY, btree (id)

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 &gt; cybersoftbjv1.sql

How to dump database cleanly?

% pg_dump -c  -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 &gt; 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()),
to_timestamp(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP())),
to_timestamp(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP()))::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)

How to show the bytea_output for a client connection?

SHOW bytea_output

How to kill client connection for a database?

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE datname = 'sample_database';
SELECT pg_terminate_backend(23240) FROM pg_stat_activity WHERE datname = 'sample_database';

Show the biggest tables in MB

SELECT 
  table_schema, 
  TABLE_NAME, 
  pg_size_pretty(pg_relation_size(table_schema || '.' || TABLE_NAME)) AS size_in_mb, 
  pg_relation_size(table_schema || '.' || TABLE_NAME) AS SIZE
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema','pg_catalog') 
ORDER BY SIZE DESC;