In PostgreSQL, you can use the pg_sleep_for() function to delay execution for a given interval.

It makes the current session’s process sleep until the specified interval has elapsed.

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 goes like this:

pg_sleep_for(interval)

Where interval is an interval that specifies the interval before the process continues.

Example

Here’s an example to demonstrate its usage.

\x
SELECT 
  clock_timestamp(),
  pg_sleep_for('2 minutes 10 seconds'),
  clock_timestamp(),
  pg_sleep_for('1 minute 5 seconds'),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-28 15:14:26.53039+10
pg_sleep_for    |
clock_timestamp | 2020-06-28 15:16:36.595837+10
pg_sleep_for    |
clock_timestamp | 2020-06-28 15:17:41.671152+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.

Negative Values

Providing a negative value will result in the statement running immediately.

SELECT 
  clock_timestamp(),
  pg_sleep_for('-2 minutes 10 seconds'),
  clock_timestamp(),
  pg_sleep_for('-1 minute 5 seconds'),
  clock_timestamp();

Result (using vertical output):

clock_timestamp | 2020-06-29 09:01:09.468232+10
pg_sleep_for    |
clock_timestamp | 2020-06-29 09:01:09.468291+10
pg_sleep_for    |
clock_timestamp | 2020-06-29 09:01:09.468297+10