10 tips and tricks for PostgreSQL (part 2)

Published on Mar. 27, 2017 by Gabriel Bordeaux

Index

This article is the 2nd part of my "10 tips and tricks for PostgreSQL". If you did not read the first part yet, you can find it here.

Here is the full index of my tips and tricks for PostgreSQL:

6. Manage NULLs in your ORDER BY

Let's create the following table for our example:

DROP TABLE IF EXISTS foo;
CREATE TEMP TABLE foo (a int);
INSERT INTO foo VALUES (1), (NULL), (4), (2), (NULL), (3);

If we select the data from this table and order them by "a" DESC, we will have the NULL values first:

gab@test> SELECT *
FROM foo
ORDER BY a DESC;
   a    
--------
 {NULL}
 {NULL}
      4
      3
      2
      1
(6 rows)

You can use the PostgreSQL instruction NULLS FIRST or NULLS LAST to let PostgreSQL know where you want the NULLs to be, for example:

gab@test> SELECT *
FROM foo
ORDER BY a DESC NULLS LAST;
   a    
--------
      4
      3
      2
      1
 {NULL}
 {NULL}
(6 rows)

7. Access data from the previous and the next row with lag() and lead()

For this example, let's create a table with the weather forecast for New York City:

DROP TABLE IF EXISTS weather_new_york;
CREATE TEMP TABLE weather_new_york (date date, temperature numeric);
INSERT INTO weather_new_york VALUES ('2014-07-06', 86), ('2014-07-07', 88), ('2014-07-08', 91), ('2014-07-09', 88), 
                                    ('2014-07-10', 86), ('2014-07-11', 84), ('2014-07-12', 86), ('2014-07-13', 86);

Here is our data:

gab@test> SELECT date, temparature
FROM weather_new_york
ORDER BY date;
    date    | temparature 
------------+-------------
 2014-07-06 |          86
 2014-07-07 |          88
 2014-07-08 |          91
 2014-07-09 |          88
 2014-07-10 |          86
 2014-07-11 |          84
 2014-07-12 |          86
 2014-07-13 |          86
(8 rows)

With the PostgreSQL Window Functions lag() and lead() we can access for each row, the data from the previous and from the next row, for example:

gab@test> SELECT date, temparature, 
                                    lag(temparature, 1) OVER(ORDER BY date) as day_before, 
                                    lead(temparature, 1) OVER(ORDER BY date) as day_after,
                                    (temparature - lag(temparature, 1) OVER(ORDER BY date)) as difference_from_day_before
FROM weather_new_york ORDER BY date;
    date    | temparature | day_before | day_after | difference_from_day_before 
------------+-------------+------------+-----------+----------------------------
 2014-07-06 |          86 |     {NULL} |        88 |                     {NULL}
 2014-07-07 |          88 |         86 |        91 |                          2
 2014-07-08 |          91 |         88 |        88 |                          3
 2014-07-09 |          88 |         91 |        86 |                         -3
 2014-07-10 |          86 |         88 |        84 |                         -2
 2014-07-11 |          84 |         86 |        86 |                         -2
 2014-07-12 |          86 |         84 |        86 |                          2
 2014-07-13 |          86 |         86 |    {NULL} |                          0
(8 rows)

8. Difference between two dates

You can easily subtract two dates with the following query:

gab@test> SELECT EXTRACT(epoch FROM '2014-07-06'::timestamp - '2014-01-01'::timestamp) / 86400 AS difference;
 difference 
------------
        186
(1 row)

9. Export the result from a query to CSV

Let's create a basic table:

DROP TABLE IF EXISTS foo;
CREATE TEMP TABLE foo (a int, b text);
INSERT INTO foo VALUES (1, 'alpha'), (89, 'beta'), (4, 'gamma'), (2, 'delta'), (6, 'epsilon'), (3, 'zeta');

There are 2 solutions: the Postgresql's built in COPY command and the psql (command-line client) \copy command. With the built in COPY command, the server will write in the file directly as the opposite of the psql \copy command where psql will first receive the data and then write it to the CSV file. I personally prefer the psql \copy command because you do not have to be a superuser to use it (you need to be a superuser to use the COPY command).

Export to CSV with the built in COPY command

Here is an example using the built in COPY command:

COPY (
   SELECT * FROM foo
)
TO '/tmp/foo.csv'
WITH CSV HEADER;
COPY 6
Time: 0.390 ms

Export to CSV with the psql \copy command

Here is an example using the built in COPY command:

\copy (Select * From foo) To '/tmp/test.csv' With CSV
Time: 0.621 ms

Note that the command needs to be on one line and that there is no final ";" because commands are terminated by newline, unlike SQL commands.

In both of these cases, the CSV file will be identical, here is its content in our example:

a,b
1,alpha
89,beta
4,gamma
2,delta
6,epsilon
3,zeta

10. Customize psql with the .psqlrc file

And last but not least, a very useful thing is to improve the PostgreSQL command-line experience with the .psqlrc file. You can create the following file in your home : ~/.psqlrc.

Here are a few examples of customizations I use:

-- toggles expanded table formatting mode
\x auto

-- Customize the promp
-- in the following example, it will look like:
-- [my_server] gab@test #  
\set PROMPT1 '[my_server] %n@%/%x %# '

-- By default, command processing continues after an error. When this variable is set, it will instead stop immediately
\set ON_ERROR_STOP

-- The on_error_rollback-on mode works by issuing an implicit SAVEPOINT for you, just before each command
-- that is in a transaction block, and rolls back to the savepoint on error.
\set ON_ERROR_ROLLBACK

-- Sets the string to be printed in place of a null value
\pset null '{NULL}'

-- toggles a display of how long each SQL statement takes, in milliseconds
\timing