It is the time of The 10th Annual PostgreSQL Conference Europe, so this is the perfect time to blog about some tips and tricks around psql you’ll love. psql is such a powerful tool that you really should use it every day. It saves you so much work and is packed with features that makes your life so much easier. In this post we’ll look at some features you maybe didn’t know before.
Lets start with something very simple: You probably know the “\l” shortcut to display all the databases:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
Did you know you also can pass the shortcuts from your shell directly into psql?
postgres@pgbox:/home/postgres/ [PGDEV] psql -c '\l' postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
But there is even a faster way for retrieving that information:
postgres@pgbox:/home/postgres/ [PGDEV] psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
Did you know you can log the complete psql session to a logfile?
postgres@pgbox:/home/postgres/ [PGDEV] psql -X -L /var/tmp/log postgres
psql (12devel)
Type "help" for help.
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# select 2;
?column?
----------
2
(1 row)
postgres=# \! cat /var/tmp/log
********* QUERY **********
select 1;
**************************
?column?
----------
1
(1 row)
********* QUERY **********
select 2;
**************************
postgres=#
You probably know that copy is the fastest way to get data into and out of PostgreSQL. Did you know you can copy from a program?
postgres=# create table lottery ( draw_date date, winning_numbers text, mega_ball integer, multiplier integer );
CREATE TABLE
postgres=# copy lottery from
program 'curl https://data.ny.gov/api/views/5xaw-6ayf/rows.csv?accessType=DOWNLOAD'
with (header true, delimiter ',', format csv);
COPY 1713
postgres=# select * from lottery limit 5;
draw_date | winning_numbers | mega_ball | multiplier
------------+-----------------+-----------+------------
2002-05-17 | 15 18 25 33 47 | 30 |
2002-05-21 | 04 28 39 41 44 | 9 |
2002-05-24 | 02 04 32 44 52 | 36 |
2002-05-28 | 06 21 22 29 32 | 24 |
2002-05-31 | 12 28 45 46 52 | 47 |
(5 rows)
That basically means, whatever “program” you use: As long as the result is something psql understands you can use it.
How often do you dynamically build SQL statements you want to execute right after? There is a quite effective solution for that in psql:
postgres=# select 'create table t'||i||'( a int )' from generate_series(1,10) i; \gexec
?column?
---------------------------
create table t1( a int )
create table t2( a int )
create table t3( a int )
create table t4( a int )
create table t5( a int )
create table t6( a int )
create table t7( a int )
create table t8( a int )
create table t9( a int )
create table t10( a int )
(10 rows)
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
Did you know you can store the result of a query into a variable and use that later in other statements?
postgres=# select 3 as var; \gset var ----- 3 (1 row) postgres=# \echo :var 3 postgres=# select * from lottery where multiplier = :var; draw_date | winning_numbers | mega_ball | multiplier ------------+-----------------+-----------+------------ 2011-02-18 | 05 06 07 30 45 | 42 | 3 2011-03-01 | 01 12 19 20 47 | 25 | 3 2011-04-01 | 13 14 35 36 53 | 19 | 3 2011-04-08 | 06 40 45 50 56 | 11 | 3 2011-04-15 | 22 23 33 39 48 | 29 | 3 2011-04-22 | 03 18 46 51 53 | 17 | 3 2011-04-26 | 19 29 32 38 55 | 15 | 3 2011-05-06 | 06 18 26 37 41 | 9 | 3 2011-05-24 | 09 12 21 42 43 | 42 | 3 2011-05-31 | 28 30 31 37 55 | 13 | 3 2011-06-03 | 20 23 41 49 53 | 31 | 3 2011-06-10 | 18 21 27 37 38 | 7 | 3 ...
The last one for today is one of my favorites: As with the Linux watch command you can watch in psql:
postgres=# select now();
now
-------------------------------
2018-10-23 21:57:17.298083+02
(1 row)
postgres=# \watch
Tue 23 Oct 2018 09:57:19 PM CEST (every 2s)
now
-------------------------------
2018-10-23 21:57:19.277413+02
(1 row)
Tue 23 Oct 2018 09:57:21 PM CEST (every 2s)
now
-------------------------------
2018-10-23 21:57:21.364605+02
(1 row)
Btw: You can see that the PostgreSQL Conference Europe is a technical conference when you take a look at the exhibition area during the sessions: Almost empty 🙂
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)