In PostgreSQL, you can use the pg_sleep() function to delay execution for a given number of seconds.

It makes the current session’s process sleep until the specified number of seconds have elapsed. It works similar to MySQL‘s sleep() function, and also TSQL‘s waitfor statement in SQL Server.

The pg_sleep() function accepts a double precision argument, so you can also specify fractional-second delays.

The sleep delay will be at least as long as specified. However, it could be longer depending on factors such as server load, as well as your platform’s effective resolution of the sleep interval.

Syntax

The syntax is quite simple:

pg_sleep(seconds)

Where seconds is a double precision value that specifies how many seconds/fractional seconds must pass before the process continues.

Example

Here’s an example to demonstrate its usage.

\x
SELECT 
  clock_timestamp(),
  pg_sleep(1),
  clock_timestamp(),
  pg_sleep(1),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-28 13:46:53.808072+10
pg_sleep        |
clock_timestamp | 2020-06-28 13:46:54.870423+10
pg_sleep        |
clock_timestamp | 2020-06-28 13:46:55.938278+10

I used clock_timestamp() in this example, because it changes during statement execution. This allows us to see the updated values as the statement progresses.

I used expanded display (sometimes referred to as “vertical output”) in this example in order to make it easier to see the result.

You can toggle expanded display in psql by using \x.

Fractional Seconds

As mentioned, you can specify fractional-second delays if required.

SELECT 
  clock_timestamp(),
  pg_sleep(.5),
  clock_timestamp(),
  pg_sleep(.5),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-28 15:03:52.496211+10
pg_sleep        |
clock_timestamp | 2020-06-28 15:03:53.041063+10
pg_sleep        |
clock_timestamp | 2020-06-28 15:03:53.553291+10

Negative Values

Negative values will result in no delay to execution.

SELECT 
  clock_timestamp(),
  pg_sleep(-1),
  clock_timestamp(),
  pg_sleep(-1),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-29 09:03:19.056681+10
pg_sleep        |
clock_timestamp | 2020-06-29 09:03:19.056682+10
pg_sleep        |
clock_timestamp | 2020-06-29 09:03:19.056683+10