Watching sql statements in PostgreSQL

Back in 2012 I wrote a small blog post about watching the results of commands in Linux. Well, the same can be done in psql:

(postgres@[local]:4448) [postgres] > create table t1 ( a int );
CREATE TABLE
(postgres@[local]:4448) [postgres] > insert into t1 values ( generate_series ( 1, 10));
INSERT 0 10
(postgres@[local]:4448) [postgres] > select count(*) from t1;
 count 
-------
    10
(1 row)

(postgres@[local]:4448) [postgres] > watch 
Watch every 2s	Mon Dec 21 07:34:35 2015

 count 
-------
    10
(1 row)

Watch every 2s	Mon Dec 21 07:34:37 2015

 count 
-------
    10
(1 row)

Watch every 2s	Mon Dec 21 07:34:39 2015

 count 
-------
    10
(1 row)

Isn’t that cool? Timing information can be displayed as well:

(postgres@[local]:4448) [postgres] > timing
Timing is on.
(postgres@[local]:4448) [postgres] > select count(*) from t1;
 count 
-------
    10
(1 row)

Time: 1.355 ms
(postgres@[local]:4448) [postgres] > watch
Watch every 2s	Mon Dec 21 07:35:50 2015

 count 
-------
    10
(1 row)

Time: 0.246 ms
Watch every 2s	Mon Dec 21 07:35:52 2015

 count 
-------
    10
(1 row)

Time: 0.264 ms
Watch every 2s	Mon Dec 21 07:35:54 2015

 count 
-------
    10
(1 row)

Time: 0.267 ms

Btw: You can adjust the interval at which the statement is executed by specifying the seconds after the “watch” command:

(postgres@[local]:4448) [postgres] > select count(*) from t1;
 count 
-------
    10
(1 row)

Time: 0.254 ms
(postgres@[local]:4448) [postgres] > watch 3
Watch every 3s	Mon Dec 21 07:36:51 2015

 count 
-------
    10
(1 row)

Time: 0.250 ms
Watch every 3s	Mon Dec 21 07:36:54 2015

 count 
-------
    10
(1 row)

Time: 0.268 ms
Watch every 3s	Mon Dec 21 07:36:57 2015

 count 
-------
    10
(1 row)

Time: 0.267 ms

Another small, but very useful feature. Have fun watching your statements.