Essential PostgreSQL

By Leo Hsu and Regina Obe

About Postgresql

PostgreSQL is the world’s most advanced open source database. It runs on numerous platforms: Linux, Unix, Windows, Mac OSX. It is simple to install, fast, sports enterprise features such as advanced spatial support via PostGIS, windowing functions, and table partitioning. It supports almost all SQL-92, SQL:1999, SQL:2003 and many SQL:2006 and SQL:2008 standards. In addition to its enterprise features, it has the added benefit of supporting numerous languages for authoring stored functions and an extensible procedural language architecture to introduce new languages.

Targeted at novices and professionals alike, this Refcard will help you quickly navigate some of PostgreSQL’s most popular features as well as its hidden gems. It will cover topics such as configuration, administration, backup, language support, and advanced SQL features. Items marked with [8.4] were introduced in PostgreSQL 8.4.

Configuration

PostgreSQL uses three configuration files to control overall operations. You can find these files in the initialized data cluster (the folder specified during the initialization process using initdb -d).

postgresql.conf

Many of these service settings can be overridden for each session, database, or user/role.

pg_hba.conf

PostgreSQL supports many authentication schemes to control access to the database. The pg_hba.conf file dictates which schemes are used based on the rules found in this file. You can mix and match various authentication schemes at the same time. The rules are applied sequentially such that the first match fitting a connection is the one that is used. This is important to remember because if you have a more restrictive rule above a less restrictive, then the more restrictive is the one that trumps.

The most commonly used authentication schemes are trust (which allows connections without a password) and md5 which authenticates with md5 encrypted passwords. Others include: reject, crypt, password (this is plain text), krb5, ident (authenticate simply by identity of user in OS), pam, ldap.

The example pg_hba.conf entries below allows all local connections to connect to all databases without a password and all remote connections to authenticate via md5.

Data types

PostgreSQL has numerous built-in types. In addition, you can define custom types. Furthermore, all tables are considered to be types in their own right, and can therefore be used within another table’s column. Below are the common built-in types.

Data and Time Types

Numeric Types

String Types

Other Types

Common Global Variables

Commonly Used Func tions

DateTime Functions and Operators

Example:
date_part(‘day’, timestamp ‘2009-07-04 11:05:45’) => 4
date_part(‘hour’, interval ‘560 minutes’) => 9

Text Functions and Operators

Left and right pad
lpad(‘A’, 5, ‘X’) => XXXXA
rpad(‘A’, 5, ‘X’) => AXXXX

Array Functions

Array concatenation ARRAY[1,2,3] || ARRAY[3,4,5] => {1,2,3,3,4,5}

Converts an array to rows SELECT anum FROM unnest(ARRAY[1,2,3]) AS anum

Other Functions

Database objects

Here is a listing of what you will find in a PostgreSQL server or database. An * means the object lives at the server level, not the database level.

Tools

PostgreSQL comes bundled with several tools useful for administration and query writing.

PSQL Common Ta sks

PSQL is a command-line tool that allows you to run ad-hoc queries, scripts, and other useful database management routines. PSQL runs in both a non-interactive mode (straight from the OS shell prompt) and an interactive mode (PSQL terminal prompt). In both modes, the following arguments apply:

PSQL Non-Interactive Mode

Getting help

psql –help

Execute an SQL script stored in a file

psql –h localhost -U postgres –p 5432 –f pgdumpall.sql

Output data in html format

psql -h someserver -p 5432 -U postgres -d dzone -H -c “SELECT * FROM
pg_tips” -o mydata.html

Execute a single statement against a db

psql -U postgres –p 5432 -d dzone -c “CREATE TABLE test(some_id
serial PRIMARY KEY, some_text text);”

Execute an SQL batch script against a database and send output to file

psql -h localhost -U someuser -d dzone -f scriptfile.sql -o outputfile.txt

PSQL Interactive Mode

To initiate interactive PSQL, type

psql –U username –p 5432 –h localhost –d dzone

Once you are in the the psql terminal you can perform a myriad of tasks. Below are some of the common ones.

ad min tasks

Backup and Restore

Below are common backup and restore statements

Create a compressed backup

pg_dump -h someserver -p 5432 -U someuser -F c -b -v -f “somedb.
backup” somedb

Create a compressed backup of select tables

pg_dump -h localhost -p 5432 -U someuser -F c -b -f “somedb.backup”
-t “someschema.table1” -t “someschema.table2” -v somedb

Create a compressed backup excluding a particular schema

pg_dump -h localhost -p 5432 -U someuser -F c -b -f “somedb.backup”
-N someschema -v somedb

Restore a compressed backup

pg_restore –h localhost –d db_to_restore_to –U someuser somedb.backup

Restore select schemas from backup

pg_restore –h localhost –d db_to_restore_to –U someuser -n
someschema1 -n someschema2 somedb.backup

Output a table of contents from backup file

pg_restore -l -f “toc.txt” “somedb.backup”

Restore only items in the table of contents

pg_restore -h localhost -d db_to_restore -U someuser -L “toc.txt”
“somedb.backup”

Below are common switches used with pg_dump [D], pg_ restore [R], pg_dumpall [A]. These tools are packaged with PostgreSQL and are in the bin folder. They are also packaged with pgAdmin III and are in the PgAdmin III/1.10/ folder.

User Rights Management

These are SQL commands you can use to control rights. They can be run in the PSQL interactive, via an SQL script, or via PgAdmin.

Data Defini tion (DDL)

Many of the examples we have below use named schemas. If you leave out the schema, objects created will be in the first schema defined in the search_path and dropped by searching the search path sequentially for the named object.

CREATE OR REPLACE FUNCTION sometrigger()
		RETURNS trigger AS
		$$
		BEGIN
		IF OLD.somecolumn <> NEW.somecolumn OR (OLD.
		somecolumn IS NULL AND NEW.somecolumn IS NOT NULL)
		THEN
		NEW.sometimestamp := CURRENT_TIMESTAMP;
		END IF;
		RETURN NEW;
		END;
		$$
		LANGUAGE ‘plpgsql’ VOLATILE;

Query and Upda te (DML )

These are examples that showcase some of PostgreSQL query features.

Retrieving data

-- this example selects the store and product
-- where the given store has the lowest price
-- for the product. This uses PostgreSQL
-- DISTINCT ON and an order by to resort
-- results by product_name.
SELECT r.product_id, r.product_name, r.product_price
FROM (SELECT DISTINCT ON(p.product_id) p.product_
id, p.product_name, s.store_name, i.product_price
FROM products AS p INNER JOIN inventory As i
ON p.product_id = i.product_id
INNER JOIN store AS s ON i.store_id = s.store_id
ORDER BY p.product_id, i.product_price) As r;

--numbering restarts for each category
SELECT ROW_NUMBER() OVER(PARTITION BY
p.product_category ORDER BY p.product_price),
p.product_category, p.product_price
FROM products AS p
ORDER BY p.product_category, p.product_price

	
WITH c AS
( SELECT country_code, conv_us
FROM country
), prices AS
(SELECT p.car, p.category, p.price*c.conv_us As us_price
FROM cars As p
INNER JOIN c
ON p.country_code = c.country_code
)
SELECT DISTINCT ON(category)
category, car, us_price
FROM prices
ORDER BY category, us_price;
	

WITH RECURSIVE tree AS
(SELECT id, parentid,
CAST(item As text) As fullname
FROM products
WHERE parentid IS NULL
UNION ALL
SELECT p.id,p.parentid,
CAST(t.fullname || ‘->’
|| p.item As text) As fullname
FROM products As p
INNER JOIN tree AS t
ON (p.parentid = t.id)
)
SELECT id, fullname
FROM tree;

Adding and Updating Data

INSERT INTO tableA(id,price)
SELECT invnew.id,invnew.price
FROM tableB As invnew LEFT JOIN tableA As invold ON
(invnew.id = invold.id)
WHERE invold.price IS NULL;

UPDATE tableA
SET price = invnew.price
FROM tableB AS invnew
WHERE invnew.id = tableA.id
AND NOT (invnew.price = tableA.price);

Procedural Lan guages

PostgreSQL stands out from other databases in its extensive and extendable support for different languages to write database stored functions. It allows you to call out to libraries native to that language. We will list the key as well as some esoteric ones. The ones with * are preinstalled with PostgreSQL and can be enabled. Some require additional installs in addition to the language handler.

You can create set returning functions, simple scalar functions, triggers, and aggregate functions with most of these languages. This allows for languages highly optimized for a particular task to work directly with data without having to always copy it out to process as you normally would need to with a simple database storage device. Language handlers can be of two flavors trusted and untrusted. An untrusted language can access the file system directly.

CREATE PROCEDURAL LANGUAGE ‘plpythonu’ HANDLER plpython_call_handler;
CREATE OR REPLACE somename(arg1 arg1type)
RETURNS result_argtype AS
$$
body goes here
$$
LANGUAGE ‘somelang’;

Not always enabled but packaged so can be installed.
CREATE FUNCTION cp_upd(p_key integer, p_value
varchar)
RETURNS void AS
$$
BEGIN
IF EXISTS(SELECT test_id FROM testtable WHERE test_id
= p_key) THEN
UPDATE testtable
SET test_stuff = p_value
WHERE test_id = p_key;
ELSE
INSERT INTO testtable (test_id,
test_stuff)
VALUES(p_key, p_value);
END IF;
RETURN;
END;
$$
LANGUAGE ‘plpgsql’ VOLATILE;

CREATE OR REPLACE FUNCTION use_quote(TEXT)
RETURNS text AS $$
my $text_to_quote = shift;
my $qfunc = $_SHARED{myquote};
return &$qfunc($text_to_quote);
$$ LANGUAGE plperl;

CREATE FUNCTION fnfileexists(IN fname text) RETURNS
boolean AS
$$
import os
return os.path.exists(fname)
$$
LANGUAGE ‘plpythonu’;

Good for doing advanced stats and plotting using R
statistical language.
CREATE FUNCTION r_quantile(float8[])
RETURNS float8[] AS
$$
quantile(arg1, probs = seq(0, 1, 0.25),
names = FALSE)
$$ LANGUAGE ‘plr’;

Allows to write in shell script
CREATE FUNCTION callscript(id integer) RETURNS text AS
$$
#!/bin/sh
wget -q “http://somesite.com/somepage?id=$1” >/dev/
null 2>&1
echo “done”
$$
LANGUAGE ‘plsh’ VOLATILE;

Common Procedural Tasks

Create a trigger and use in table

CREATE FUNCTION mytable_ft_trigger() RETURNS trigger AS $$
BEGIN
NEW.tsv :=
setweight(to_tsvector(‘pg_catalog.english’,
coalesce(new.field1,’’)), ‘A’) ||
setweight(to_tsvector(‘pg_catalog.english’,
coalesce(NEW.field2,’’)), ‘B’);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER mytable_trigiu BEFORE INSERT OR UPDATE
ON mytable FOR EACH ROW EXECUTE PROCEDURE mytable_ft_trigger();

Return sets and use of out params

CREATE OR REPLACE FUNCTION
fn_sqltestmulti(param_subject varchar,
OUT test_id integer,
OUT test_stuff text)
RETURNS SETOF record
AS
$$
SELECT test_id, test_stuff
FROM testtable
WHERE test_stuff LIKE $1;
$$
LANGUAGE ‘sql’ STABLE;
--example
SELECT * FROM fn_sqltestmulti(‘%stuff%’);

Create an aggregate function

CREATE AGGREGATE sum(text) (
SFUNC=textcat,
STYPE=text,
INITCOND=’’
);
SELECT item, sum(category || ‘ ‘) as categories
FROM item_cat GROUP BY item;

About The Author

The wife and husband team of Leo Hsu and Regina Obe founded Paragon Corporation in 1997 specializing in database technology. Paragon Corporation works with numerous organizations to design, develop and maintain database and web applications. In 2002, Leo and Regina started to dabble in the growing field of spatial analysis and have become active participants in the on-going development of PostGIS which is a spatial extension of PostgreSQL. Regina is a member of the PostGIS core development team and Project Steering Committee.

Through Paragon Corporation, Leo and Regina have helped many clients using PostgreSQL, SQL Server and MySQL. Paragon Corporation takes on database projects in a wide range of industries and advocates database-driven development.

Leo and Regina met at MIT and both graduated with engineering degrees. Leo went on to obtain a master’s degree from Stanford University in Engineering of Economic Systems.

They maintain two sites: http://www.postgresonline.com -- provides tips and tricks for using PostgreSQL and http://www.bostongis.com - provides tips and tricks for using PostGIS, SQL Server 2008 Spatial and other open source and open GIS tools.

Email contact: lr@pcorp.us

URL: http://www.paragoncorporation.com

Recommended Book

PostGIS in Action is the first book devoted entirely to PostGIS. It will help both new and experienced users write spatial queries to solve real-world problems. For those with experience in more traditional relational databases, this book provides a background in vector-based GIS so you can quickly move to analyzing, viewing, and mapping data. Advanced users will learn how to optimize queries for maximum speed, simplify geometries for greater efficiency, and create custom functions suited specifically to their applications. It also discusses the new features available in PostgreSQL 8.4 and provides tutorials on using additional open source GIS tools in conjunction with PostGIS.

---

BUY NOW

http://www.manning.com/obe