071 BROUGHT TO YOU BY:

Essential PostgreSQL
Get More Refcardz! Visit Refcardz.com

» About PostgreSQL
C O NT E NT S » Configuration
» Data Types
» Commonly Used Functions
» Database Objects and more...
By Leo Hsu and Regina Obe

A BOUT POSTGR ESQ L OPTION DESCRIPTION

listen_ Use ‘*’ to listen on all IPs of the server,

PostgreSQL is an open-source object-relational database addresses ‘localhost’ to listen on just local, or a comma
with many enterprise-level features. It runs on numerous separated list of IPs to listen on. Requires
platforms: Linux, Unix, Windows, and Mac OS X. It is simple service restart if changed and can only be set
and quick to install, fast, and it sports advanced features globally.
such as: streaming replication, spatial support via PostGIS, port Defaults to 5432, but can be changed to
windowing functions, table partitioning, and full-text allow multiple postgresql daemon clusters/
search. In addition to its enterprise features, it has the added versions to coexist using same IP but
benefit of supporting numerous languages for authoring different ports.
stored functions. It has an extensible procedural language search_path List of default schemas that don’t need
architecture to introduce new languages. It also has an schema qualification. First schema is where
extensible type and index architecture for introducing new non-schema qualified objects are created.
data types, operators, and indexes for these custom types, and constraint_ Options: on, off, or partial. Partial was
support for querying external data sources such as CSV, web exclusion introduced in 8.4 and is the new default.
services, and other PostgreSQL services via its Foreign Data Allows planner to skip over tables if
Wrapper (SQL/MED) support. constraint ensures query conditions cannot
be satisfied by the table. Mostly used for table
Targeted at novices and professionals alike, this Refcard will partitioning via table inheritance.
help you quickly navigate some of PostgreSQL’s most popular
shared_buffers Controls how much memory is allocated to
features as well as its hidden gems. It will cover topics such as PostgreSQL and shared across all processes.
configuration, administration, backup, language support, and Requires service restart and can only be set
advanced SQL features. There will be a special focus on new globally.
features in PostgreSQL 9.3 and 9.4.
In PostgreSQL 9.4, a new SQL construction ALTER SYSTEM was
CO N F I G U R AT I O N introduced that allows you to set these settings at the system
level without editing the postgresql.conf. For many, you still
PostgreSQL uses three main configuration files to control need to do a service restart and for others at least a:
overall operations. You can find these files in the initialized
data cluster (the folder specified during the initialization SELECT pg_reload_conf();

process using initdb -d).

PG_HBA.CONF
All these can be edited with a text editor. They can PostgreSQL supports many authentication schemes to control
HOT be edited via PgAmin III if you install the adminpack access to the database. The pg_hba.conf file dictates which
TIP extension in master postgres db.
To do so: CREATE EXTENSION ADMINPACK;
schemes are used based on the rules found in this file. You
can mix and match various authentication schemes at the

FILE PURPOSE

postgresql.conf Controls the listening port, IP, and default

query planner settings, memory settings,
ESSENTIAL POSTGRESQL

path settings, and logging settings. Can be

queried via pg_settings database view.

pg_hba.conf Controls the authentication models used

by PostgreSQL and can be set per user, per Real-Time
database, per IP range, or a combination of
all.
Big Data Using
pg_indent.conf Controls mapping of an OS user to a PostgreSQL
PostgreSQL user. Deliver real-time insights into your
big data to your end users while
POSTGRESQL.CONF
reducing the cost and complexity
of your database infrastructure
The following settings are all located in the postgresql.conf
file. Remember that these are default settings; many of these www.citusdata.com
you can choose to override for each session, for each database,
or for each user/role.

© D Z O NE, INC. | DZ O NE .C O M
2 ESSENTIAL POSTGRESQL

same time. The rules are applied sequentially such that the
first match fitting a connection is the one that is used. This is NUMERIC TYPES
important to remember because if you have a more restrictive TYPE DESCRIPTION
rule above a less restrictive, then the more restrictive is the one
int, int8 4 byte and 8 byte integers.
that trumps.

The most commonly used authentication schemes are trust serial, serial4, Sequential integers; this can be used during
serial8 table creation to specify auto-numbered
(which allows connections without a password) and md5
fields.
(which authenticates with md5 encrypted passwords). Others
include: reject, crypt, password (this is plain text), krb5, ident numeric(s, p) Decimal numbers; s is scale and p is
precision.
(authenticate simply by identity of user in OS), pam, and ldap.
double precision Floating point numbers.
The example pg_hba.conf entries below allow all local
connections to connect to all databases without a password and numrange, Introduced in 9.2 for defining number
all remote connections to authenticate via md5. int4range ranges.

#TYPE DATABASE USER CIDR-ADDRESS METHOD An integer >= 1 and < 10.
SELECT ‘[1,10)’::int4range;
HOST ALL ALL 127.0.0.1/32 TRUST
HOST ALL ALL 0.0.0.0/0 MD5 percentile_cont, Continuous and discrete percentile
percentile_disc aggregate. Can take a numeric value
(between 0 and 1) for percentile rank or can
DATA T Y P E S take an array of numeric values between 0
and 1.
PostgreSQL has numerous built-in types. In addition, you can
define custom types. Furthermore, all tables are considered to STRING TYPES
be types in their own right, and can therefore be used within TYPE DESCRIPTION
another table’s column. Below are the common built-in types:
varchar(n) (a.k.a. Max of n characters, no trailing spaces.
DATE/TIME TYPES character varying)

TYPE DESCRIPTION char(n) Padded to n characters.

date The date is a datatype to represent dates text Unlimited text.

with no time. Default representation is ISO
8601 e.g. ‘YYYY-MM-DD’. Use datestyle
configuration setting to control defaults. OTHER TYPES
TYPE DESCRIPTION
timestamp This includes both date and time and is
timezone-neutral. array Arrays in PostgreSQL are typed and you can create
Example: ‘2009-07-01 23:00’ an array of any type. To define a column as an array
of a specific type, follow with brackets. Example:
timestamp with Example: ‘2009-07-01 23:00:00-04’ varchar(30)[]. You can also autogenerate arrays in
time zone SQL statements with constructs such as:
time Time without date. SELECT ARRAY['john','jane'];
SELECT ARRAY(SELECT emp_name FROM employees);
Example: ‘23:14:20’ SELECT array_agg(e.emp_name) FROM employees;

time with time Example: ‘23:14:20-04’ enum Enumerators:

zone CREATE TYPE cloth_colors AS ENUM
('red','blue','green');
interval A unit of time used to add and subtract from
a timestamp. When used in a table, you define the column as the
name of the enum. Sorting is always in the order the
Example: SELECT TIMESTAMP 2009-07-01
23:14:20' + INTERVAL '4 months 2 days items appear in the enum definition.
10 hours 9 seconds' boolean True/false.
daterange, tsrange, New in PostgreSQL 9.2; defines a specific
tstzrange time range. Example is a date > 2012-07-01 bytea Byte array used for storing binary objects, such as
and <= 2013-08-31 files.

SELECT '(2012-07-01, lo Large object. Stored in a separate system table with

2013-08-31]'::daterange; object ID reference to the large object. Useful for
importing files from file system and storing and
Constituents of Century, day, decade, dow (starts Sunday),
exporting back to file system.
datetime, use doy, epoch, hour, isodow (day of week starts
date_part function on Monday), minute, month, quarter, JSON JavaScript Object Notation (JSON) was introduced
to extract week, year. in PostgreSQL 9.2 and includes built-in validation.
JSON stored as plain text. No direct index support.
PostgreSQL 9.3 enhanced JSON functionality by
providing more functions and operators that work
with JSON. PostgreSQL 9.4 enhanced further by
providing even more functions and operators.

© D Z O NE, INC . | DZ O NE .C O M
3 ESSENTIAL POSTGRESQL

TYPE DESCRIPTION TYPE DESCRIPTION

jsonb Binary form of JSON—introduced in PostgreSQL Quotes keywords and expressions not
9.4. Can be indexed using GIN indexes and supports suitable for identity when unquoted.
quote_ident
intersects and containment operators in addition quote_ident('in') => "in"
to all the functions and operators JSON supports. quote_ident('big') => big
Performance is much faster than the JSON type. No
duplicate keys per object are allowed; sort of keys per quote_literal Escapes both single and double quotes.
object are not maintained.
Similar to quote_literal but doesn’t
quote_nullable
quote NULL.
COMMON GLOBAL VARIABLES
replace('1234abcv', '1234', 'joe') =>
TYPE DESCRIPTION replace joeabcv
CURRENT_ Takes a delimited string and returns the nth
Returns current date and time with timezone.
TIMESTAMP, now() split_part item.

CURRENT_DATE Returns current date without the time. split_part('abc|def', '|', 2) =>def

SQL aggregate function that aggregates a

CURRENT_TIME Returns current time without the date. string_agg
set of values into a string.

strpos(text, subtext) Returns numeric position of subtext within text.

CO M M O N LY U S E D F U N C T I O N S
trim, btrim, ltrim,
Trim spaces in string.
DATE/TIME FUNCTIONS AND OPERATORS rtrim
TYPE DESCRIPTION

Returns an interval spanned ARRAY FUNCTIONS AND OPERATORS

age(timestamp1,
between timestamp1 and TYPE DESCRIPTION
timestamp2)
timestamp2.
Array concatenation.
age(timestamp) Difference from current time. || ARRAY[1,2,3] || ARRAY[3,4,5] =>
{1,2,3,3,4,5}
date_part('day', timestamp
date_part(text, timestamp), '2009-07-04 11:05:45') => 4 Converts an array to rows.
unnest
date_part(text, interval) date_part('hour', interval '560 SELECT anum FROM unnest(ARRAY[1,2,3])
minutes') => 9
SQL aggregate function that aggregates a
date_trunc('hour', '2014-01-15 array_agg
date_trunc(text, timestamp set of values into an array.
10:30 PM'::timestamp) => 2014-
| timestamptz | date) 01-15 22:00:00
array_upper(anyarray, Returns upper/lower bound of the
You can add (or subtract) intervals dimension) requested array dimension.
to datetimes. You can perform SELECT array_upper(ARRAY[ARRAY['a'],
operators +, -, / (for array_lower(anyarray,
addition and subtraction between ARRAY['b']], 1);
intervals only) dimension)
two datetimes. You can divide outputs: 2
intervals into smaller intervals.
Converts an array to a text delimited by the
array_to_
generate_ delimiter.
string(anyarray,
series(timestamp, Generate rows of timestamps. array_to_string(ARRAY[12,34], '|') =>
delimiter_text)
timestamp, [interval]) [8.4] '12|34'

STRING FUNCTIONS AND OPERATORS RANGE FUNCTIONS AND OPERATORS

TYPE DESCRIPTION TYPE DESCRIPTION

|| (string || string, Lower bound and upper bound value of a range:

String concatenation.
string || number) SELECT lower(a), upper(a)
lower(anyrange), FROM (SELECT '[1,10]'::int4range AS a) AS
Returns left x elements, right x elements, or f;
left, right, substring substring from position x for y number upper(anyrange) outputs:
of elements. lower | upper
------+------
1 | 11
length Number of characters in string.
Contains range or element.
Left and right pad.
SELECT a @> 1 AS ce,
lpad, rpad lpad('A', 5, 'X') => 'XXXXA' @> a @> '[2,3]'::int4range AS cr
rpad('A', 5, 'X') => 'AXXXX' FROM (SELECT '[1,10]'::int4range AS a) AS
f;
lower, upper, initcap Lower, upper, proper case.
&& Have elements in common.
md5 Calculates the MD5 hash.

© D Z O NE, INC . | DZ O NE.C O M

4 ESSENTIAL POSTGRESQL

TYPE DESCRIPTION OTHER FUNCTIONS

TYPE DESCRIPTION
Union of 2 ranges.
SELECT '[2014-7-20, generate_series(int1, int2,
2014-10-20]'::daterange + '[2014-6-20, [step]) Returns rows consisting of numbers
+ 2014-7-22]'::daterange; from int1 to int2 with [step] as
generate_ gaps. Step is optional and defaults
Output: series(timestamp1, to 1.
[2014-06-20,2014-10-21) timestamp2, [interval])

Intersection. min, max, sum, avg, count Common aggregates.

SELECT '[2014-7-20,
2014-10-20]'::daterange * '[2014-6-20, percentile_dist, percentile_
Useful for computing medians.
* 2014-7-22]'::daterange; cont [9.4]
Output:
[2014-07-20,2014-07-23)
DATA B A S E O B J E C TS
Difference.
Here is a listing of what you will find in a PostgreSQL server or
SELECT '[2014-7-20,
2014-10-20]'::daterange - '[2014-6-20, database. An * means the object lives at the server level, not the
- 2014-7-22]'::daterange; database level.
Output:
OB JECT DESCRIPTION
[2014-07-20,2014-10-21)
Databases* PostgreSQL supports more than one database per
service/daemon.
JSON/JSONB FUNCTIONS AND OPERATORS
Tablespaces* Logical representation of physical locations
TYPE DESCRIPTION
where tables are stored. You can store different
Extract an element of JSON/jsonb as text. tables in different tablespaces, and control data
SELECT prod->>'price' As price storage based on database and user/group role.
->> FROM (
SELECT '{"id": 1, "name": "milk", Languages These are the procedural languages installed in
"price": 2.50}'::json as prod) As f; the database.
outputs:2.50
Casts PostgreSQL has the unique feature of having an
Extract an element of JSON/jsonb as JSON/ extensible cast system. It has built-in casts, but
jsonb allows you to define your own and override default
->
(useful for doing more operations on a casts. Casts allow you to define explicit behavior
complex subelement). when casting from one object to another, and
Extract a nested element of JSON/jsonb as allow you to define autocast behavior.
text. Schemas These are logical groupings of objects. One can
SELECT prod#>>'{nutrition,vitamin think of them as mini-databases within a larger
d}'::text[] AS vd
database. An object always resides in a schema.
#>> FROM (
SELECT '{"id": 1,"name": "milk", Tables, Views Views are virtual tables that encapsulate an
"price": 2.50, "nutrition": {"vitamin
d": "30%"}}'::json AS prod) AS f; SQL SELECT statement. In PostgreSQL, tables
can inherit from other tables and data can be
Outputs: 30% altered against views. PostgreSQL 9.1+ introduced
Extract a nested element of JSON/jsonb as Foreign Tables, which are references to data
#> JSON/jsonb. Useful for doing more operations from a Foreign source via a foreign data wrapper
such as working with arrays within json. (FDW). PostgreSQL 9.3 introduced materialized
views, which are views that contain the cached
WINDOW FUNCTIONS data. These need to be refreshed to update the
view cache.
TYPE DESCRIPTION
Rules Rules are similar to triggers, except they can only
Number of current row from its current
row_number be written in SQL, and they rewrite a statement
partition.
rather than actually updating directly. Views are
rank, percent_ Ranking based on order in current partition actually implemented as SELECT rules (and can
rank, dense_ (dense_rank is without gaps; percent_rank is have DO INSTEAD inserts/update rules to make
rank relative rank). them updateable).

Nth value relative to current, -nth value Functions, These can be written in any enabled language
lead, lag relative to current (n defaults to 1) in current Triggers, and in the database, live in schemas. PostgreSQL
partition. Aggregates allows you to define your own custom aggregate
functions. Triggers are special classes of
first_value, last_ Absolute first/last/nth value in a partition
functions that have OLD and NEW variables
value, nth_value based on order regardless of current position.
available that hold a pointer to the OLD and NEW
data. Triggers are bound to table. New in

© D Z O NE, INC . | DZ O NE.C O M

5 ESSENTIAL POSTGRESQL

OB JECT DESCRIPTION PSQL runs in both a non-interactive mode (straight from the OS
shell prompt) and an interactive mode (PSQL terminal prompt).
Functions, PostgreSQL 9.3 are event triggers which are
In both modes, the following arguments apply:
Triggers, and bound to events such as creation of table or
Aggregates (cont.) deletion of table.
ARGUM ENT DESCRIPTION
Operators, Live in schemas. Many are predefined, but more
Database. Defaults to the user (via system
Operator Classes, can be added and allow you to define things such -d
identification if no user is specified).
Operator Families as +, =, etc. for custom data types.
-h Server host. Defaults to localhost if not specified.
Sequences Autocreated when defining columns as serial. In
PostgreSQL, sequences are objects in their own -p Port. Defaults to 5432 if not specified.
right and can be shared across many tables.
Username you are trying to log in with. Defaults to
Types Live in schemas. Don’t forget that you have the -U
system user name.
flexibility to create your own custom data types in
PostgreSQL.
PSQL NON-INTERACTIVE MODE
Foreign Data Foreign Data Wrappers are remote data source
Getting help
Wrappers, drivers that allow you to access data in a
Servers and User non-PostgreSQL or remote PostgreSQL table. $ psql –help
Mappings PostgreSQL 9.1 introduced these. 9.2 improved on
Execute an SQL script stored in a file
general performance, and 9.3 introduced a new
FDW called postgresfdw for connecting to other $ psql –h localhost -U postgres –p 5432 –f /path/to/
PostgreSQL servers, and also enhanced the API to pgdumpall.sql
support Foreign table updates.
Output data in html format
Extensions Packaging of functions, tables, and other objects $ psql -h someserver -p 5432 -U postgres -d dzone -H -c
[9.1+] for easy deployment in a database. These are "SELECT * FROM pg_tips" -o tips.html
installed using CREATE EXTENSION.
Execute a single statement against a db
CREATE EXTENSION hstore;
$ psql -U postgres –p 5432 -d dzone -c "CREATE TABLE
test(some_id serial PRIMARY KEY, some_text text);"
TOOLS
Execute an SQL batch script against a database and send output to file
PostgreSQL comes bundled with several tools useful for
$ psql -h localhost -U someuser -d dzone -f /path/to/
administration and query writing. scriptfile.sql -o /path/to/outputfile.txt

TOOL DESCRIPTION
PSQL INTERACTIVE MODE
psql Command-line client packaged with PostgreSQL.
Good for automating SQL jobs, copying data, To initiate interactive PSQL, type:
outputing simple HTML reports. psql –U username –p 5432 –h localhost –d dzone

createdb, For creating and dropping a database from the

dropdb OS shell. Once you are in the PSQL terminal there are a myriad of tasks
you can perform. Below are some of the common ones.
pgAdminIII Popular graphical user interface packaged with
PostgreSQL.
COM M AND TA SK
pg_restore Command-line tool for restoring compressed or
\q Quit
.tar backups.

pg_dump Command-line tool for doing backups. Great for :q Cancel out of more screen
automated backups.
\? Help on psql commands
pg_dumpall Command-line tool for dumping all databases
into a single backup. \h some_command Help on SQL commands

pgAgent A daemon/service that can be downloaded from \connect postgres Switch database
http://www.pgadmin.org/download/pgagent.php.
\l List all databases
Used for scheduling SQL jobs and batch shell
jobs. Jobs can be added easily and monitored \dtv p* List tables and views that start with p.
using the PgAdmin III job interface.
\du List user/group roles and their group
pg_basebackup Used for doing filesystem hot backup of db memberships and server level permissions.
data cluster.
\d sometable List columns, data types, and constraints
pg_upgrade Used for updating in place from one major for a table.
version of PostgreSQL to another.
\i somefile Execute SQL script stored in a file.

PS Q L CO M M O N TA S K S \o somefile Output contents to file.

PSQL is a command-line tool that allows you to run ad-hoc Use up and down Retrieve prior commands.
arrows
queries, scripts, and other useful database management routines.

© D Z O NE, INC . | DZ O NE.C O M

6 ESSENTIAL POSTGRESQL

COM M AND TA SK SWITCH TOOL DESCRIPTION

\timing Toggle query timing on and off; when on, -b, --blobs D Include large objects in dump.
query output includes timing information.
Specify name of database to restore
\copy Copy from client computer to server and -d, --dbname=NAME R
to.
from server to client computer. Example:
The following command string copies data Specify backup file format (c =
to local client computer in CSV format with compressed, t = tar, p = plain text,
header. -F, --format=c|t|p|d DR d = directory). Plain-text backups
must be restored with psql.
\copy (SELECT * FROM sometable) TO
'C:/sometable.csv' WITH HEADER CSV Directory new in [9.2].
FORCE QUOTE
Clean (drop) schema prior to create
\copy ... from Allows you to copy output from an external -c, --clean DRA (for pg_dumpall drop database prior
program program such as ls, dir, wget, curl. New in to create).
9.3.
Dump only global objects (roles,
-g, --globals-only A schemas, tablespaces), no
A D M I N TA S K S databases.

Use this multiple parallel jobs to

BACKUP AND RESTORE restore. This is especially useful
Below are common backup and restore statements. for large backups and speeds them
-j, --jobs=NUM up significantly in many cases.
Create a compressed backup [8.4],[9.2]
DR
8.4 introduced parallel restore
pg_dump -h someserver -p 5432 -U someuser -F -c -b -v -f (pg_restore). 9.2 introduced (in
"/somepath/somedb.backup" somedb pg_dump) parallel backup (needs to
have format directory based).
Create a compressed backup of select tables
Print summarized TOC of the
pg_dump -h localhost -p 5432 -U someuser -F -c -b -f -l, --list R
archive.
"C:/somedb.backup" -t "someschema.table1" -t "someschema.
table2" -v somedb -L, --use- Use TOC from this file for selcting/
R
list=filename ordering output.
Create a compressed backup excluding a particular schema
Dump/restore only select objects in
pg_dump -h localhost -p 5432 -U someuser -F -c -b -f -n, --schema=NAME DR
"C:/somedb.backup" -N someschema -v somedb schema(s).

-N, --exclude- Exclude from dump/restore named

Restore a compressed backup DR
schema=SCHEMA schema.
pg_restore –h localhost –d db_to_restore_to –U someuser
/path/to/somedb.backup Dump only roles, no database or
-r, --roles-only A
tablespace.
Restore select schemas from backup Backup only named table(s) along
pg_restore –h localhost –d db_to_restore_to –U someuser -t, --table=NAME D with associated indexes, constraints,
-n someschema1 -n someschema2 /path/to/somedb.backup and rules.

Output a table of contents from backup file -T, --exclude- Exclude named table(s) from
D
table=NAME backup.
pg_restore -l -f "C:/toc.txt" "C:/somedb.backup"
-v --verbose DRA Controls verbosity.
Restore only items in the table of contents
--exclude-table- Exclude dumping table data for
pg_restore -h localhost -d db_to_restore -U someuser -L D
"C:/toc.txt" "C:/somedb.backup" data=TABLE [9.2] specific table.

Dump or restore select parts.

OTHER -s –section=pre- Pre-data just backs up or restores
data|post- DR structures; post-data restores
Change globally work mem (9.4+) data|data [9.2] primary keys, foreign keys, and
Requires reload and some require restart. constraints. Data just restores data.
ALTER SYSTEM SET work_mem TO '20MB';
SELECT pg_reload_conf(); --if-exists [9.4] D Use IF EXISTS when dropping.

pg_dumpall currently only dumps to plain text sql. pg_dumpall

USER RIGHTS MANAGEMENT
HOT backups must be restored with psql. For space savings and
flexibility, use pg_dump. With pg_dump compressed and
TIP
These are SQL commands you can use to control rights. They can
tar backups, you can selectively restore objects. You cannot
selectively restore with plain text backups. be run in the PSQL interactive, loading an SQL file, or via
PgAdmin.
Below are common switches used with pg_dump [D], pg_restore CREATE ROLE somerole LOGIN
Create a new role with
[R], pg_dumpall [A]. These tools are packaged with PostgreSQL login rights that can create NOSUPERUSER INHERIT CREATEDB
and are in the bin folder. They are also packaged with pgAdmin objects NOCREATEROLE;
III and are in the PgAdmin III/version/ folder.

© D Z O NE, INC . | DZ O NE.C O M

7 ESSENTIAL POSTGRESQL

Create a group role with no CREATE ROLE somerole NOSUPERUSER Create or alter a CREATE OR REPLACE VIEW someview AS SELECT
login rights and members INHERIT NOCREATEDB NOCREATEROLE; view * FROM sometable;
inherit rights of role [Prior to version 8.4, adding new columns to a view
Add a role to another role GRANT somerole TO someotherrole; requires dropping and recreating].

Create a CREATE MATERIALIZED VIEW someview AS

Give rights to a role Example uses:
materialized view SELECT * FROM sometable;
GRANT SELECT, UPDATE ON TABLE
sometable TO somerole; Refresh REFRESH MATERIALIZED VIEW someview;
GRANT ALL ON TABLE sometable TO materialized view
somerole;
Refresh REFRESH MATERIALIZED VIEW CONCURRENTLY
GRANT EXECUTE ON FUNCTION materialized view someview;
somefunction TO somerole;
without read
-- Grant execute to all users blocking [9.4]
GRANT EXECUTE ON FUNCTION
somefunction TO public; Create a view CREATE OR REPLACE VIEW someview AS
(doesn’t allow SELECT * FROM sometable
Revoke rights REVOKE ALL ON TABLE sometable FROM WHERE active = true WITH CHECK OPTION;
somerole; insert if data would
not be visible in
Give insert/update rights GRANT INSERT, UPDATE (somecolumn) view) [9.4]
to select columns ON sometable TO somerole;
Add a column to a ALTER TABLE sometable ADD COLUMN
Grant rights to all future ALTER DEFAULT PRIVILEGES IN SCHEMA table somecolumn timestamp NOT NULL DEFAULT
tables in a schema someschema CURRENT_TIMESTAMP;
GRANT ALL ON TABLES TO somerole;
Add a functional CREATE INDEX idx_someindex ON sometable
Grant rights to all existing GRANT ALL ON ALL TABLES IN SCHEMA index to a table USING btree (upper(somecolumn));
tables in a schema someschema TO somerole;
Create a new type CREATE TYPE sometype AS (somecolumn
integer, someothercolumn integer[]);
DATA D E F I N I T I O N (D D L) Create a trigger CREATE OR REPLACE FUNCTION sometrigger()
RETURNS trigger AS
Many of the examples we have below use named schemas. If you $$
BEGIN
leave out the schema, objects created will be in the first schema IF OLD.somecolumn <> NEW.somecolumn OR
defined in the search_path and dropped by searching the search (OLD.somecolumn IS NULL AND
NEW.somecolumn IS NOT NULL) THEN
path sequentially for the named object. NEW.sometimestamp := CURRENT_
TIMESTAMP;
Create a new CREATE DATABASE postgresql_dzone; END IF;
database RETURN NEW;
END;
Install extension in CREATE EXTENSION hstore; $$
a database LANGUAGE 'plpgsql' VOLATILE;

Create a schema CREATE SCHEMA someschema; Add trigger to table CREATE TRIGGER sometrigger BEFORE UPDATE
ON sometable FOR EACH ROW
Changing database Sets the default schema to someschema.
schema search path EXECUTE PROCEDURE sometriggerupdate();
ALTER DATABASE postgresql_dzone SET
search_path = someschema, public; Suppress redundant A built-in trigger that prevents updates that would
updates not change any data.
Dropping A drop without a CASCADE clause will not drop an
objects with no object if there are objects that depend on it, such as CREATE TRIGGER trig_01_suppress_redundant
dependencies views, functions, and tables. BEFORE UPDATE ON sometable FOR EACH ROW

For drop database you should be connected to a EXECUTE PROCEDURE suppress_redundant_

database other than the one you’re dropping. updates_trigger();
DROP DATABASE postgresql_dzone;
DROP VIEW someview;
HOT
A table can have multiple triggers, and each trigger for a
ALTER TABLE sometable DROP COLUMN particular event on a table is run in alphabetical order of the
somecolumn;
TIP
named trigger. So if order is important, name your triggers such
DROP FUNCTION somefunction; that they are sorted in the order you need them to run.
Dropping object and DROP SCHEMA someschema CASCADE;
all dependencies.
Q U E RY A N D U P DAT E (D M L)
(Use with caution.)

Create a table CREATE TABLE test_scores(student These are examples that show case some of PostgreSQL popular
varchar(100),
score integer, test_date date DEFAULT or unique query features.
CURRENT_DATE,
CONSTRAINT pk_test_scores PRIMARY KEY ADDING AND UPDATING DATA
(student, test_date));
Insert statement with INSERT INTO test_
Create a child table CREATE TABLE somechildtable (CONSTRAINT multirows scores(student,score,test_date)
pk_somepk PRIMARY KEY (id)) INHERITS VALUES ('robe', 95, '2014-01-15'),
(someparenttable); ('lhsu', 99, '2014-01-15'),
('robe', 98, '2014-07-15'),
Create a check ALTER TABLE sometable ADD CONSTRAINT ('lhsu', 92, '2014-07-15'),
constraint somecheckcontraint CHECK (id > 0); ('lhsu', 97,'2014-08-15');

© D Z O NE, INC . | DZ O NE.C O M

8 ESSENTIAL POSTGRESQL

Insert statement INSERT INTO tableA(id,price) Filtered SELECT date_trunc('quarter',test_

SELECT invnew.id,invnew.price date)::date AS qtr_start,
from SELECT, only aggregates [9.4]
FROM tableB AS invnew LEFT JOIN tableA array_agg(score) FILTER (WHERE student
load items not AS invold ON (invnew.id = invold.id) use instead of = 'lhsu') AS lhsu,
already in table WHERE invold.price IS NULL; CASE WHEN array_agg(score) FILTER (WHERE student
(or subselect) = 'robe') AS robe
Cross update, only UPDATE tableA FROM test_scores
(especially useful
update items for SET price = invnew.price GROUP BY date_trunc('quarter',test_date);
for aggregates like
a particular store FROM tableB AS invnew qtr_start | lhsu | robe
WHERE invnew.id = tableA.id array_agg which
where price has ------------+---------+------
AND NOT (invnew.price = tableA.price); may return nulls 2014-01-01 | {99} | {95}
changed 2014-07-01 | {92,97} | {98}
with CASE WHEN)
Insert from a tab- SELECT student,
COPY products FROM "/tmp/productslist. Ordered
delimited file no txt" WITH DELIMITER '\t' NULL AS 'NULL'; string_agg(score::text, ',' ORDER BY
aggregates, list
header test_date DESC) AS scores
scores in order of FROM test_scores
Insert from a test date, one row GROUP BY student;
--these copy from the server’s file system
comma-delimited for each student. student | scores
COPY products FROM "/tmp/productslist. Cast to make a ---------+----------
file with header csv" WITH CSV HEADER NULL AS 'NULL';
string. lhsu | 97,92,99
row robe | 98,95
Copy data to --this outputs to the server’s file system WITH c AS
Non-Recursive
comma-delimited COPY (SELECT * FROM products WHERE ( SELECT country_code, conv_us
CTE with 2 CTE
file and include product_rating = 'A') TO '/tmp/ FROM country
productsalist.csv' WITH CSV HEADER NULL
expressions. Note WHERE country IN('Japan', 'USA','Germany')
header a CTE expression ),
AS 'NULL';
has only one prices AS
(SELECT p.car, p.fuel_grade, price*c.
RETRIEVING DATA WITH, each
conv_us AS us_price
subexpression is FROM cars AS p
View running SELECT * FROM pg_stat_activity; separated by a , INNER JOIN c
queries and the final query ON p.country_code = c.country_
follows. code
Select the first --this example selects the store and product WHERE p.category = 'Cars'
--where the given store has the lowest price )
record of each Example returns the
--for the product. This uses PostgreSQL SELECT DISTINCT ON(fuel_grade)
distinct set of data --DISTINCT ON and an order by to resort lowest priced car
prices.car, us_price
--results by product_name. in each fuel_grade, FROM prices
limiting to just ORDER BY fuel_grade, us_price;
SELECT r.product_id, r.product_name,
r.product_price Japan, USA, German
FROM (SELECT DISTINCT ON(p.product_id) WITH RECURSIVE tree AS
p.product_id, p.product_name, s.store_ Recursive CTE *
(SELECT id, item, parentid,
name, i.product_price inventory, gives
CAST(item AS text) AS fullname
FROM products AS p INNER JOIN inventory full name which FROM products
AS i includes parent WHERE parentid IS NULL
ON p.product_id = i.product_id UNION ALL
tree name e.g.
INNER JOIN store AS s ON i.store_id SELECT p.id,p.item, p.parentid,
= s.store_id Paper->Color-
CAST(t.fullname || '->'
ORDER BY p.product_id, i.product_price) >Red->20 lbs || p.item AS text) AS fullname
AS r; FROM products AS p
SELECT DISTINCT ON(student) student, INNER JOIN tree AS t
Get last date’s ON (p.parentid = t.id)
score, test_date
score for each )
FROM test_scores
student. Returns ORDER BY student, test_date DESC; SELECT id, fullname
only one record FROM tree
ORDER BY fullname;
per student

Use window SELECT row_number() OVER(wt) AS rn,

student, test_date,
function to
(AVG(score) OVER(wt))::numeric(8,2) AS P RO C E D U R A L L A N G U A G E S
number records avg_run
and get running FROM test_scores PostgreSQL stands out from other databases with its extensive
average WINDOW wt AS (PARTITION BY student
and extendable support for different languages to write
ORDER BY test_date);
database-stored functions. It allows you to call out to libraries
rn | student | test_date | avg_run
----+---------+------------+--------- native to that language. We will list the key language as well as
1 | lhsu | 2014-01-15 | 99.00
2 | lhsu | 2014-07-15 | 95.50
some esoteric ones. The languages with an * are preinstalled
3 | lhsu | 2014-08-15 | 96.00 with PostgreSQL and can be enabled. Some require further
1 | robe | 2014-01-15 | 95.00 installs in addition to the language handler.
2 | robe | 2014-07-15 | 96.50

Get median values SELECT student, percentile_cont(0.5) You can create set returning functions, simple scalar functions,
WITHIN GROUP (ORDER BY score) AS m_ triggers, and aggregate functions with most of these languages.
[9.4]
continuous,
percentile_disc(0.5) This allows for languages that are highly optimized for a
WITHIN GROUP (ORDER BY score) AS m_ particular task to work directly with data without having to
discrete
always copy it out to process as you normally would need with a
FROM test_scores GROUP BY student;
simple database storage device. Language handlers can be of two
student | m_continuous | m_discrete
--------+--------------+------------ flavors: trusted and untrusted. An untrusted language can access
lhsu | 97 | 97
robe | 96.5 | 95
the filesystem directly.

© D Z O NE, INC . | DZ O NE.C O M

9 ESSENTIAL POSTGRESQL

From PostgreSQL 9.1+, languages not enabled by default in

EXAMPLE FUNCTIONS
database or not built-in are installed using :
This next table demonstrates some examples of writing
CREATE EXTENSION …;
functions in various languages. For all functions you write, you
CREATE EXTENSION 'plpythonu';
CREATE OR REPLACE somename(arg1 arg1type) can use the CREATE or REPLACE FUNCTION construction to overwrite
RETURNS result_argtype AS existing functions that take same arguments. We use CREATE
$$
body goes here FUNCTION here.
$$
LANGUAGE 'somelang'; L ANGUAGE E X A M PLE

CREATE FUNCTION use_quote(TEXT)

RETURNS text AS $$
L ANGUAGE DESCRIPTION REQ
plperl (trusted), my $text_to_quote = shift;
Enabled in all databases. Allows you to plperlu (untrusted) my $qfunc = $_SHARED{myquote};
return &$qfunc($text_to_quote);
write simple functions and set returning
$$ LANGUAGE plperl;
functions in just SQL. The function
internals are visible to the planner, so in CREATE FUNCTION fnfileexists(IN fname
many cases it performs better than other text) RETURNS boolean AS
plpythonu, $$
functions since the planner can strategize import os
plpython2u,
how to navigate based on the bigger plpython3u (untrusted) return os.path.exists(fname)
sql* query. It is simple and fast, but limited in $$
functionality. none LANGUAGE 'plpythonu' STRICT;
(trusted)
CREATE OR REPLACE FUNCTION prod_ Good for doing advanced stats and plotting
state(prev numeric, e1 numeric, e2
using the R statistical language.
numeric).
RETURNS numeric AS CREATE FUNCTION r_quantile(float8[])
$$ plr RETURNS float8[] AS
SELECT COALESCE($1,0) + $$
COALESCE($2*$3,0); quantile(arg1, probs = seq(0, 1, 0.25),
$$ names = FALSE)
LANGUAGE 'sql' IMMUTABLE; $$ LANGUAGE 'plr' IMMUTABLE STRICT;

Built in and always enabled. Often used to Allows you to write functions in JavaScript.
extend PostgreSQL (e.g. postgis, pgsphere, CREATE FUNCTION
tablefunc) or, for example, to introduce fib(n int) RETURNS int AS $$
new windowing functions (introduced in function fib(n) {
plv8 return n<2 ? n : fib(n-1) +
PostgreSQL 8.4). Functions are referenced
c* none fib(n-2)
from a .so or .dll file. }
CREATE OR REPLACE FUNCTION return fib(n)
st_summary(geometry) $$ LANGUAGE plv8 IMMUTABLE STRICT;
RETURNS text AS
'$libdir/postgis-2.1', 'LWGEOM_summary'
LANGUAGE 'c' IMMUTABLE STRICT; COMMON PROCEDURAL TASKS

Not always enabled, but packaged so it can Create a table trigger and use in table
be installed.
CREATE OR REPLACE FUNCTION mytable_ft_trigger()
CREATE FUNCTION cp_upd(p_key integer, RETURNS trigger AS $$
p_value varchar) BEGIN
RETURNS void AS NEW.tsv :=
$$ setweight(to_tsvector('pg_catalog.english',
BEGIN coalesce(new.field1,'')), 'A') ||
IF EXISTS(SELECT test_id FROM setweight(to_tsvector('pg_catalog.english',
testtable WHERE test_id = p_key) THEN coalesce(NEW.field2,'')), 'B');
plpgsql* UPDATE testtable return NEW;
(trusted) SET test_stuff = p_value
none
END
WHERE test_id = p_key; $$ LANGUAGE plpgsql;
ELSE
INSERT INTO testtable (test_id, CREATE TRIGGER mytable_trigiu
test_stuff) BEFORE INSERT OR UPDATE OF field1,field2
VALUES(p_key, p_value); ON mytable
END IF; FOR EACH ROW EXECUTE PROCEDURE mytable_ft_trigger()
RETURN; Return sets and use out of params
END;
$$ CREATE OR REPLACE FUNCTION
LANGUAGE 'plpgsql' VOLATILE; fn_sqltestmulti(param_subject varchar,
OUT test_id integer,
Good for manipulating JSON objects,
OUT test_stuff text)
reusing existing Javascript libraries, RETURNS SETOF record
numeric processing. Comes packaged AS
with 3 language bindings: Plv8 (aka PL/ $$
Javascript), plls (LiveScript), plcoffee Google SELECT test_id, test_stuff
plv8 (trusted)
(CoffeeScript).
v8 FROM testtable
engine WHERE test_stuff LIKE $1;
To install: $$
LANGUAGE 'sql' STABLE;
CREATE EXTENSION plv8;
--example
CREATE EXTENSION plls;
SELECT * FROM fn_sqltestmulti('%stuff%');
CREATE EXTENSION plcoffee;

© D Z O NE, INC . | DZ O NE.C O M

10 ESSENTIAL POSTGRESQL

Return sets and use of table construct E XTENSION DESCRIPTION LINK

CREATE OR REPLACE FUNCTION http://www.

fn_sqltestmulti(param_subject varchar) postgresql.org/
RETURNS TABLE(test_id integer, test_stuff text) pg_stat_ Tracks execution statistics
docs/current/static/
AS statements of all SQL statements.
pgstatstatements.
$$
SELECT test_id, test_stuff html
FROM testtable Columnar store for https://github.com/
WHERE test_stuff LIKE $1; cstore_fdw
PostgreSQL. citusdata/cstore_fdw
$$
LANGUAGE 'sql' STABLE; https://github.com/
postgresql- Distinct value counting with
aggregateknowledge/
hll tunable precision.
EXTENSIONS postgresql-hll

Extensions extend the capabilities of PostgreSQL by providing http://www.

additional data types, functions, index types, and more. After postgresql.org/
pgcrypto Cryptographic functions.
docs/current/static/
installing an extension, you need to run the following command
pgcrypto.html
to enable it:
http://www.
Connections to other
CREATE EXTENSION extension_name; postgresql.org/
dblink PostgreSQL databases from
docs/current/static/
a database session.
dblink.html
NOTABLE EXTENSIONS
E XTENSION DESCRIPTION LINK
For a full list of extensions shipped with PostgreSQL

PostGIS
Adds support for geographic
objects allowing location http://postgis.net/
HOT see: http://www.postgresql.org/docs/current/static/
contrib.html
queries to be run using SQL. TIP To search for third party extensions see: http://pgxn.org/
Shards and replicates tables
https://github.com/
pg_shard for horizontal scaling and
citusdata/pg_shard
high availability.

ABOUT THE AUTHORS RECOMMENDED BOOK

The wife and husband team of Leo Hsu and Regina Obe founded “Thinking of migrating to PostgreSQL? This clear, fast-paced
Paragon Corporation in 1997, which specializes in database introduction helps you understand and use this open source
technology and works with numerous organizations to design, database system. Not only will you learn about the enterprise
develop, and maintain database and web applications. They have class features in versions 9.2, 9.3, and 9.4, you’ll also discover
become active participants in the on-going development of PostGIS, that PostgeSQL is more than a database system—it’s also an
a spatial extension of PostgreSQL. Regina is a member of the PostGIS impressive application platform.”
core development team and Project Steering Committee. They
maintain two sites: http://www.postgresonline.com -- provides tips
and tricks for using PostgreSQL and http://www.bostongis.com - provides tips and BUY NOW
tricks for using PostGIS and other open source and open GIS tools.

CREDITS:
Editor: G. Ryan Spain | Designer: Yassee Mohebbi | Production: Chris Smith | Sponsor Relations: Brandon Rosser | Marketing: Chelsea Bosworth

BROWSE OUR COLLECTION OF 250+ FREE RESOURCES, INCLUDING:

RESEARCH GUIDES: Unbiased insight from leading tech experts
REFCARDZ: Library of 200+ reference cards covering the latest tech topics
COMMUNITIES: Share links, author articles, and engage with other tech experts

JOIN NOW

DZONE, INC.
150 PRESTON EXECUTIVE DR.
CARY, NC 27513
DZone communities deliver over 6 million pages each month to more than 3.3 million software 888.678.0399
developers, architects and decision makers. DZone offers something for everyone, including news, 919.678.0300
tutorials, cheat sheets, research guides, feature articles, source code and more.
REFCARDZ FEEDBACK WELCOME
"DZone is a developer's dream," says PC Magazine. [email protected]

©© DZONE,
form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher.
DZONE, INC. INC. |
Copyright © 2015 DZone, Inc. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any SPONSORSHIP OPPORTUNITIES
DZONE.COM
DZONE.COM
[email protected] VERSION 1.0 $7.95