Range Types
Ever had start_date and end_date columns? Have you written queries like this to find overlapping date ranges?:
… WHERE ('2014-07-15 15:20:00’ >= start_date
AND '2014-07-15 15:20:00’ < end_date)
OR ('2014-07-15 15:50:00’ > start_date
AND '2014-07-15 15:50:00’ <= end_date);
Well this isn’t necessary in PostgreSQL
CREATE TABLE appointments (
id serial primary key,
appointment tstzrange,
details text);INSERT INTO appointments (appointment, details)
VALUES ('[2014-07-15 15:00:00,2014-07-15 15:30:00)’, 'Dentist’);SELECT *
FROM appointments
WHERE appointment && '[2014-07-15 15:20:00, 2014-07-15 15:50:00)’::tstzrange;
Square brackets mean “including”, round brackets mean “from/to but not including”. So the appointment is from 15:00, and up to, but not including, 15:30. We want it this way because if we had an appointment at 15:30, we don’t want it overlapping the previous appointment at all. The && operator means “overlaps”.
Arrays
Ever had several items that you wanted to store together, but had to create a column for each one? In PostgreSQL any type (even user-created ones) can be specified as an array type by adding “[]” to the end of the type name. (note: the ISBN13 type shown below is part of the core extension “lsn”)
CREATE TABLE books (isbn ISBN13, title TEXT, author text, tags TEXT[]);INSERT INTO books (isbn, title, author, tags)
VALUES
('978-0575081406','The Name of the Wind','Patrick Rothfuss', ARRAY['fantasy','fiction','epic']),
('978-0753827666','Gone Girl','Gillian Flynn',ARRAY['mystery','fiction']),
('978-0575079755','The Lies of Locke Lamora','Scott Lynch', ARRAY['fantasy','epic']);
SELECT *
FROM books
WHERE tags @> '{fantasy}';
isbn | title | author | tags
-------------------+--------------------------+------------------+------------------------
978-0-575-08140-6 | The Name of the Wind | Patrick Rothfuss | {fantasy,fiction,epic}
978-0-575-07975-5 | The Lies of Locke Lamora | Scott Lynch | {fantasy,epic}
(2 rows)
Function Languages
PostgreSQL supports not just SQL and plpgsql as function languages, but many others including Perl, Python, Bash, TCL, Sh, R, Java, Javascript, Javascript V8, LOLcode, Scheme, PHP, Ruby, Lua, or any other language you wish to add:
CREATE OR REPLACE FUNCTION divide_python(a numeric, b numeric) RETURNS numeric AS $$
if b > 0:
return a / b
$$ LANGUAGE plpython3u;
CREATE OR REPLACE FUNCTION divide_perl(numeric, numeric) RETURNS numeric AS $$
if ($_[1] > 0) {
return $_[0] / $_[1];
}
$$ LANGUAGE plperl;
Foreign Data Wrappers
PostgreSQL can connect to pretty much any external data source:
-- Create the file_fdw extension so we can read in files from disk.
CREATE EXTENSION file_fdw;
-- Create the server definition which is straight-forward for files
CREATE SERVER file FOREIGN DATA WRAPPER file_fdw;
-- Now create the definition of the foreign table that will read in the file when queried.
CREATE FOREIGN TABLE report_file (report_date date, item text, count int) SERVER file
OPTIONS (FILENAME '/tmp/report.csv', HEADER 'true', FORMAT 'csv');-- Let's query it as if it were just a regular table
SELECT * FROM report_file ORDER BY count desc;
report_date | item | count
-------------+---------+-------
2014-10-15 | Books | 15
2014-10-15 | Glasses | 5
2014-10-17 | Laptops | 3
(3 rows)
