Skip to content

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.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *