Skip to content

SELECT pg_sleep_until('#800Monies');

Pavlo recently pointed out that the pg_sleep() function in PostgreSQL has two alternatives: pg_sleep_for() and pg_sleep_until().

What do they do?


pg_sleep() itself is used to stop the execution (as example of a file with SQL commands) for the specified amount of seconds. The number of seconds can be specified as fraction.

postgres=# \timing 
Timing is on.
postgres=# SELECT pg_sleep(2.5);
(1 row)

Time: 2508,399 ms (00:02,508)

Works quite well, the execution time is almost exactly 2.5 seconds.


That is useful for short delays. But what when the waiting time is supposed to be longer? Or the execution shall wait until a specific time? Using pg_sleep() only, one has to calculate the number of seconds. pg_sleep_for() uses an interval, and the time can be specified in a more human readable form.

postgres=# SELECT pg_sleep_for('2 minutes 17 seconds');
(1 row)

Time: 137112,465 ms (02:17,112)
postgres=# SELECT 2*60 + 17;
(1 row)

Time: 8,184 ms


And then there is pg_sleep_until(), which uses a timestamp and waits until that time.

postgres=# SELECT pg_sleep_until('2020-01-17 00:58:00');
(1 row)

Time: 27683,361 ms (00:27,683)

In this example I just picked the next full minute at the time when I was writing this blog post.

All in all very useful.


But Vik has to fix one last remaining bug, which he quoted here:

postgres=# SELECT pg_sleep_until('#800Monies');
ERROR:  invalid input syntax for type timestamp with time zone: "#800Monies"
LINE 1: SELECT pg_sleep_until('#800Monies');
Time: 8,499 ms



No Trackbacks


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options