Official PostgreSQL 9.0 Documentation URL: http://www.postgresql.org/docs/9.0/static/
For more examples of new features -- check out: http://wiki.postgresql.org/index.php?title=PostgreSQL_9.0
We cover only a subset of what we feel are the most useful constructs that we could squash in a single cheatsheet page
commonly used
1
New in this release.
2
Enhanced in this release.
COMMON BUILT-IN DATA TYPES CONTRIBS AND DATATYPES
means distributed separately* deprecated dep
Below are common data types with common alternative names.
included data types in ()
Note: There are many more and one can define new types with create type. adminpack - pgAdmin admin pack
All table structures create an implicit type struct as well. auto_explain2-- explain plan logging
citext (citext) - case insensitive text type
datatype[] - e.g. varchar(50)[] (defines an array of a type) cube (cube) - multi-dimensional cube type
bit dblink - cross database/server queries
boolean earthdistance - earth dist functions (depends on cube)
bytea fuzzstrmatch - fuzzy string match
character varying(length) - varchar(length) ltree (ltree)- hierarchical tree type
character(length) - char(length) hstore2 (hstore) - key value store type
date moddatetime - moddatetime trigger
enum passwordcheck1 ensure strong passwords
double precision - float4 float8 pg_bench benchmarking queries
integer - int4 pg_buffercache - inspect buffer cache
bigint - int8 pg_trgm - trigrams for fuzzy search
network address (inet, cidr, macaddr) pgcrypto - cryptography functions
money
pgAgent* - job agent
numeric(length,precision)
built-in (non-PostGIS) geometry types pgsphere* (scircle, sellipse, spoint,sline,spolygon, spath,strans ..)
point, lseg,box,path,polygon,circle - spherical data types often used for astronomy
oid pldbg - pl debugger
serial - serial4 postgis* (geography, geometry, raster)
bigserial - serial8 - raster packaged separately pre-2.0 (integrated in 2.0+)
text jaspa* (geometry)
time without timezone - time - postgis like implementation of geometry and functions
time with timezone - timez - implemented in pljava/java instead of C/c++
timestamp without timezone - timestamp tablefunc -- crosstab queries
timestamp with timezone - timestampz
tsquery temporal* (period) -- support for time periods
tsvector uuid-ossp -- generating uuids
uuid (aka GUID) vacuum_lo - delete orphaned large objects
xml
-- although xml2 is deprecated
-- some functionality exists still
-- not present in the built-in xml
-- e.g. xslt_process function
xml2dep
Admin Functions Common Functions Date and timestamp Functions Math Operators
COPY .. FROM .. cast, :: age(timestamp[,timestamp]) % , ^, |/
COPY .. TO .. coalesce date_part(text, timestamp) ||/,!, !!
current_setting generate_series(start, stop [,step]) century @, &, |
pg_cancel_backend generate_series(start timestamp, day #,~, <<
pg_column_size stop timetamp [,step inteval]) decade >>
pg_database_size greatest(val1,val2,val3....) dow
pg_relation_size least(val1,val2,val3,...) doy Math Functions
pg_size_pretty nullif epoch
pg_tablespace_size random hour This is a subset
pg_total_relation_size minute abs
set_config second cbrt
Sequence (Serial) Functions
vacuum analyze verbose millisecond ceiling
vacuum full currval microsecond degrees
lastval(serialname) month exp
Languages nextval(serialname) quarter floor
second log
* packaged separately String Functions isodow ln
c ,plpgsql, sql2 week mod
pljava || year pi
ascii isoyear power
plperl(u)2 millennium radians
chr
plproxy* convert_from(string bytea, src_encoding name) date_trunc(text,timestamp) random
plpython2 convert_to(string text, dest_encoding name) extract(field from interval|timestamp) sqrt
encode(data bytea, ['base64', 'hex', 'escape']) interval trunc
plpython31 to_char
initcap
plr* to_date Trig Functions
length
plruby* lower to_timestamp
acos
plscheme* lpad
Date Predicates asin
plsh* ltrim
atan
pltcl md5 overlaps atan2
octet_length
cos
Command Line position(substring in string) Array Constructors cot
quote_ident
pi()
pgbench quote_literal ARRAY[[4,5,6],..] sin
pg_dump quote_nullable ARRAY() tan
pg_dumpall regexp_matches array_agg
pg_resetxlog regexp_replace array_append Enums
pg_restore regexp_split_to_array( array_cat
pg_standby string, pattern [, flags ]) array_dims > < <= >= =
regexp_split_to_table(
pg_upgrade 2 string, pattern [, flags])
array_prepend enum_cmp
psql array_to_string enum_first
repeat SOME(array) enum_larger
vacuumdb
replace regexp_split_to_array(string, pattern [, flags ]) enum_last
vacuumlo
rpad string_to_array enum_range
rtrim enum_smaller
Large Object
split_part Array Operators
Server
string_agg1 XML
lo_create strpos =
substr <> database_to_xml
lo_export, lo_import
translate(string text, from text, to text) < database_to_xmlschema
lo_unlink
> query_to_xml
trim
<= query_to_xml_and_xmlschema
Client upper
|| table_to_xml
lo_close xmlagg
Database Globals
lo_create Array Functions Other xmlattributes
lo_export, lo_import current_date xmlcomment
array_lower(anyarray, int) xmlconcat
lo_lseek current_time
array_upper(anyarray, int) xmlelement
lo_open current_timestamp
generate_subscripts(array anyarray, dim int [,reverse boolean]) xmlforest
lo_read current_user
unnest xpath
lo_tell localtime
lo_unlink xmlpi
lo_write xmlroot
JOIN Types Window Keywords DDL
CROSS JOIN BETWEEN frame_start AND frame_end1 ADD CONSTRAINT
EXCEPT (ALL) CURRENT ROW CREATE AGGREGATE
FULL JOIN ORDER BY CREATE CAST
[INNER] JOIN OVER CREATE (DEFAULT) CONVERSION
PARTITION BY CREATE DATABASE
INTERSECT (ALL) CREATE DOMAIN
RANGE | ROWS 2 CREATE [OR REPLACE] FUNCTION
LEFT JOIN
UNBOUNDED FOLLOWING CREATE (UNIQUE) INDEX
NATURAL JOIN
UNBOUNDED PRECEDING CREATE LANGUAGE
RIGHT JOIN
UNION (ALL) CREATE OPERATOR
Window Functions CREATE OPERATOR FAMILY
SQL Keywords CREATE RULE
In addition to window functions any aggregate function can be used in a window expression CREATE SCHEMA
ANY(array) cume_dist dense_rank CREATE SEQUENCE
first_value
BETWEEN .. AND
lag lead last_value CREATE TABLE2
CASE WHEN .. END
DELETE FROM ntile nth_value CREATE TABLESPACE 2
DISTINCT percent_rank rank CREATE TEXT SEARCH DICTIONARY
DISTINCT ON row_number CREATE TRIGGER 2
DO 1 ALTER TABLE
Aggregates CREATE TYPE
for anonymous functions (plpgsql, lolcode, plperl)
EXISTS CREATE [OR REPLACE] VIEW
(For all aggregates you can also use:
FROM DROP objecttype object_name[IF EXISTS]
someagg(somefield ORDER BY somefield1,....somefieldn)
GROUP BY someagg(DISTINCT somefield) ALTER ..(supported for DATABASE, TABLE, TABLESPACE)
HAVING someagg(DISTINCT somefield ALTER TABLE .. DROP COLUMN [IF EXISTS] 1
ILIKE 1 ALTER TABLE .. DROP CONSTRAINT [IF EXISTS] 1
IN(..) ORDER BY somefield)
LIKE array_agg ALTER TABLE .. ADD CONSTRAINT .. EXCLUDE1
LIMIT ..OFFSET avg
NOT bit_and, bit_or DCL
NOT IN(..) boolean_and, boolean_or
corr CREATE ROLE
NULLS FIRST
count GRANT ALL ON SCHEMA ...
NULLS LAST
covar_pop, covar_samp GRANT
ORDER BY
1 every [ALL, INSERT, SELECT, UPDATE, DELETE,
someagg(.. ORDER BY somefield1, ..somefieldn) max TRUNCATE, REFERENCES, TRIGGER]
SELECT min ON TABLES to somerole 1
SET regr_avgx, regr_avgy
SIMILAR TO GRANT [EXECUTE] ON ALL FUNCTIONS 1
regr_count
TRUNCATE TABLE regr_intercept ALTER DEFAULT PRIVILEGES IN SCHEMA1
UPDATE regr_r2 REVOKE
USING regr_slope [ALL ..]
WHERE regr_sxx
regr_sxy 1
COMMON TABLE EXPRESSION (CTE) regr_syy PostgreSQL Keywords
stddev
RECURSIVE keyword is required if any expression is RECURSIVE stddev_pop EXPLAIN ANALYZE VERBOSE
WITH [RECURSIVE] tablevar1 stddev_samp EXPLAIN (ANALYZE true, COSTS true, FORMAT json|yaml|xml) 1
AS (table_sql_def), 1
.., tablevarn AS (table_sql_defn) string_agg(expression, delimeter)
sum Key information_schema Views
final_query
variance
columns
var_pop
column_privileges
var_samp
enabled_roles
xmlagg
key_column_usage
referential_constraints
Key pg_catalog Tables/Views routines (lists all functions)
pg_class sequences
pg_rules schemata
pg_settings tables
pg_stat_activity views
pg_stat_database
pg_tablespaces
EXAMPLES OF NEW FEATURES IN POSTGRESQL 9.0 (DDL)
For some of these examples, we will use the contrived table. Allow IF EXISTS drop on columns and constraints
Demonstrates new ability to define primary/unique key deferrable ALTER TABLE passengers_tally DROP COLUMN
CREATE TABLE passengers( IF EXISTS age ;
passenger_id serial PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, ALTER TABLE passengers_tally DROP CONSTRAINT
passenger_name varchar(100), IF EXISTS pk_passengers_tally;
weight integer, aisle varchar(10));
INSERT INTO passengers(passenger_name, weight, aisle) Using exclusion constraints - PostGIS example
VALUES ('Jack', 200, '18'), ('Jill', 150, '20'), - no overlapping point bounding box
('Cathy', 150, '20') , ('Simon',1000, '18'); CREATE TABLE poi(pt_id serial PRIMARY KEY,
SELECT INTO and CREATE TABLE AS pt geography(Point,4326));
now return row counts to the client in their command tags ALTER TABLE poi ADD CONSTRAINT uidxb_poi
SELECT aisle, COUNT(*) as tally INTO passengers_tally EXCLUDE USING gist (pt WITH &&);
FROM passengers GROUP BY aisle;
-- result
SELECT 2
EXAMPLES OF NEW FEATURES IN POSTGRESQL 9.0 (DML)
-- swapping keys -- Note I can get away with this --Using WINDOW FUNCTIONS ROWS
even though the update will before completion cause a key violation because passenger_id is marked as DEFERRED SELECT aisle, passenger_name, weight,
UPDATE passengers SUM(weight) OVER (ORDER BY weight, aisle, passenger_name
SET passenger_id = ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING) As weight_look_2_ahead
CASE WHEN passenger_id = 4 THEN 1 ELSE passenger_id + 1 END; FROM passengers
ORDER BY weight,aisle,passenger_name;
--Using ORDERED aggregates and the new string_agg aggregate function -- results --
SELECT aisle, string_agg(passenger_name, '|' ORDER BY weight) As pass_list_by_wgt, aisle | passenger_name | weight | weight_look_2_ahead
string_agg(passenger_name, '|' ORDER BY passenger_name) As pass_list_name, -------+----------------+--------+---------------------
array_agg(DISTINCT weight ORDER BY weight) As arr_weight 20 | Cathy | 150 | 500
FROM passengers 20 | Jill | 150 | 1350
GROUP BY aisle ORDER BY aisle; 18 | Jack | 200 | 1200
18 | Simon | 1000 | 1000
-- results WINDOW FUNCTION RANGE - tally weight of all people of equal or lower weight in same aisle
aisle | pass_list_by_wgt | pass_list_name | arr_weight SELECT aisle, passenger_name, weight,
-------+------------------+----------------+------------ SUM(weight) OVER (PARTITION BY aisle ORDER BY weight
18 | Jack|Simon | Jack|Simon | {200,1000} RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) As weight_aisle_lower
20 | Jill|Cathy | Cathy|Jill | {150} FROM passengers
ORDER BY aisle,weight,passenger_name;
-- result --
aisle | passenger_name | weight | weight_aisle_lower
-------+----------------+--------+--------------------
18 | Jack | 200 | 200
18 | Simon | 1000 | 1200
20 | Cathy | 150 | 300
20 | Jill | 150 | 300
EXAMPLES OF NEW FEATURES IN POSTGRESQL 9.0 - FUNCTION ENHANCEMENTS
Anonymous function using new DO command function named parameters
- adds a date_add field to all tables in public schema that don't already have one CREATE FUNCTION random_data(num_records integer,
DO $$ multiplier float)
DECLARE var_searchsql text; RETURNS SETOF float AS
BEGIN $$
var_searchsql := string_agg('ALTER TABLE ' SELECT random()*i*$2
|| t.table_schema || '.' FROM generate_series(1,$1) As i;
|| t.table_name
|| ' ADD COLUMN date_add timestamp DEFAULT(current_timestamp)', ';') $$ language 'sql';
FROM information_schema.tables t
LEFT JOIN information_schema.columns c -- calling the function with named parameters --
ON (t.table_name = c.table_name AND SELECT foo.i
t.table_schema = c.table_schema AND c.column_name = 'date_add') FROM random_data(multiplier:= 1000,
WHERE t.table_type = 'BASE TABLE' AND t.table_schema = 'public' num_records:= 10) As foo(i);
AND c.table_name IS NULL;
IF var_searchsql > '' THEN
EXECUTE var_searchsql;
END IF;
END$$ language plpgsql;
EXAMPLES OF NEW FEATURES IN POSTGRESQL 9.0 (DCL)
These examples will use these roles,database, schema Granting/Revoking permissions on existing tables in schema public
CREATE ROLE jungle; GRANT ALL PRIVILEGES ON
CREATE ROLE regina LOGIN CREATEDB PASSWORD 'queen^warrior'; ALL TABLES IN SCHEMA public TO jungle;
GRANT jungle TO regina; Granting all permissions on future tables in schema public to jungle.
CREATE ROLE leo LOGIN PASSWORD 'lion@king.dom'; -- There are some nuances we won't get into such as permissions are only granted
CREATE DATABASE kingdom OWNER regina; -- to objects that the grantor has permissions to GRANT
-- using the optional [FOR role] option allows
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM jungle;
-- you to designate a different grantor role other than current user
-- as long as current user/has rights to promote to said role.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO jungle
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO jungle;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO jungle;
ADMIN EXAMPLES
SELECT pg_size_pretty(pg_tablespace_size('pg_default')) as tssize, Vacuuming
pg_size_pretty(pg_database_size('somedb')) as dbsize, vacuum analyze verbose;
pg_size_pretty(pg_relation_size('someschema.sometable')) as tblsize; vacuum sometable;
vacuum full;
--Example importing data to table sometable
--from tab delimited where NULLs appear as NULL
--Kills all active queries in selected db and list out process id
COPY sometable FROM '/path/to/textfile.txt' USING DELIMITERS '\t' WITH NULL As 'NULL'; --and usename of process and if kill successful
--Example importing data to table sometable SELECT procpid, usename, pg_cancel_backend(procpid)
--from csv delimited that includes field headers FROM pg_stat_activity
COPY sometable FROM 'C:/somefile.csv' WITH CSV HEADER; WHERE datname = 'somedb';
--Example exporting a query to a comma separated (CSV) called textfile.csv --introduced in 8.3 - terminates backedn
--setting NULLS to text NULL SELECT procpid, usename, pg_terminate_backend(procpid)
COPY (SELECT * FROM sometable WHERE somevalue LIKE '%') TO '/path/to/textfile.csv' FROM pg_stat_activity
WITH NULL As 'NULL' CSV HEADER QUOTE AS '"'; WHERE datname = 'somedb';
DDL EXAMPLES
CREATE DATABASE somedb CREATE OR REPLACE FUNCTION first_element_state(anyarray, anyelement)
WITH OWNER = someuser RETURNS anyarray AS
WITH ENCODING='UTF8' TEMPLATE=template0 TABLESPACE = pg_default $$
LC_COLLATE = 'English_United States.1252' SELECT CASE WHEN array_upper($1,1) IS NULL
LC_CTYPE = 'English_United States.1252'; THEN array_append($1,$2) ELSE $1 END;
$$ LANGUAGE 'sql' IMMUTABLE;
CREATE TABLE orders(order_id serial NOT NULL,
order_addeddt timestamp with time zone, CREATE OR REPLACE FUNCTION first_element(anyarray)
order_rating rating, RETURNS anyelement AS
CONSTRAINT pk_orders_order_id PRIMARY KEY (order_id) $$
); SELECT ($1)[1] ; $$ LANGUAGE 'sql' IMMUTABLE;
CREATE TYPE rating AS CREATE AGGREGATE first(anyelement) ( SFUNC=first_element_state,
ENUM('none', 'bronze', 'silver', 'gold', 'platinum'); STYPE=anyarray, FINALFUNC=first_element);
SELECT EXAMPLES
SELECT o.order_id, o.order_date, o.approved_date, CTE Example (8.4+)
COUNT(i.item_id) As nlineitems, WITH pt(x,y) AS (
SUM(i.unit_price*i.num_units) As total SELECT 100*random(), 200*random()
FROM orders o FROM generate_series(1,10) As i
INNER JOIN orderitems i ON o.order_id = i.order_id ),
GROUP BY o.order_id, o.order_date, o.approved_date pt2(x,y) AS
HAVING SUM(i.unit_price*i.num_units) > 200 ( SELECT generate_series(1,2) As x, generate_series(4,5) )
ORDER BY o.approved_date NULLS FIRST; SELECT pt.x + pt2.y AS a, pt2.x*pt.y AS b
FROM pt CROSS JOIN pt2;
UPDATE/INSERT/DELETE EXAMPLES
UPDATE sometable SET somevalue = 5 --This only works on 8.1+ --
WHERE sometable.somename = 'stuff';
INSERT INTO orders(order_addeddt, order_rating)
UPDATE sometable VALUES ('2007-10-01 20:40', 'gold'),
SET calccount = s.thecount ('2007-09-01 11:00 AM', 'silver'),
FROM (SELECT COUNT(someothertable.someid) as thecount, ('2007-09-02 10:00 PM', 'none'), ('2007-10-10 PM', 'bronze');
someothertable.someid
FROM someothertable --Pre 8.1+ only supports single values inserts
GROUP BY someothertable.someid) s INSERT INTO orders(order_addeddt, order_rating)
WHERE sometable.someid = s.someid; VALUES ('2007-10-01 20:40', 'gold');
DELETE FROM sometable
WHERE somevalue = 'something'; --This is a fast delete that deletes everything in a table so be cautious.
--Only works on tables not referenced in foreign key constraints
TRUNCATE TABLE sometable;
COMMAND LINE EXAMPLES
These are located in bin folder of PostgreSQL
To get more info about each do a --help e.g. psql --help
pg_dump -i -h someserver -p 5432 -U someuser -F c -b -v -f "\somepath\somedb.backup" somedb
pg_dumpall -i -h someserver -p 5432 -U someuser -c -o -f "\somepath\alldbs.sql"
pg_restore -i -h someserver -p 5432 -U someuser -d somedb -l "\somepath\somedb.backup"
psql -h someserver -p 5432 -U someuser -d somedb -f "\somepath\somefiletorun.sql"
psql -h someserver -p 5432 -U someuser -d somedb -c "CREATE TABLE sometable(st_id serial, st_name varchar(25))"
output query as xml
psql -h someserver -p 5432 -U someuser -d somedb -P "t" -c "SELECT query_to_xml('select * from sometable', false, false, 'sometable')" -o "outputfile.xml";
New -- only analyze
vacuumdb --analyze-only
http://www.postgresonline.com
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License .
Feel free to use this material, but we ask that you please retain the Postgres OnLine website link.