PostgreSQL provides various in-built functions to pause or delay the query’s execution for a specific period. For example, the pg_sleep(), pg_sleep_until(), and pg_sleep_for() are popularly used functions that allow us to pause the execution of a statement halfway. All the stated functions are very similar, but they work a little differently.

This blog will illustrate how to pause the query’s execution for a specific time period in PostgreSQL.

How to Pause Query Execution in PostgreSQL?

In Postgres, the below-enlisted functions are used to pause the execution of a statement for a certain time period:

- Method 1: pg_sleep()
- Method 2: pg_sleep_until()
- Method 3: pg_sleep_for()

How to Pause Query Execution pg_sleep() Function?

In Postgres, the pg_sleep() function delays the query’s execution for a certain number of seconds:

SELECT CLOCK_TIMESTAMP(),
pg_sleep(2),
CLOCK_TIMESTAMP(),
pg_sleep(2),
CLOCK_TIMESTAMP();

A delay of 2 seconds can be noticed in the execution time of each CLOCK_TIMESTAMP.

How to Pause Query Execution pg_sleep_until() Function?

Postgres pg_sleep until() function accepts a sleep time as an argument and puts the query execution on sleep until the wake-up time reaches:

SELECT CLOCK_TIMESTAMP(),
pg_sleep_until('11:59:02.1234'),
CLOCK_TIMESTAMP();

The output shows that the query’s execution resumes when the pg_sleep_until() reaches the specified time.

How to Pause Query Execution pg_sleep_for() Function?

The pg_sleep_for() function accepts an interval as an argument and delays the query’s execution based on the specified interval. It is used to put larger delays between the query’s execution:

SELECT CLOCK_TIMESTAMP(),
pg_sleep_for('1 minute 05 seconds'),
CLOCK_TIMESTAMP(),
pg_sleep_for('1 minute 05 seconds'),
CLOCK_TIMESTAMP();

This way, you can specify the larger delays between the query execution using the pg_sleep_for() function.

Conclusion

In PostgreSQL, various in-built functions are used to pause or delay the query’s execution for a specific period. For example, the pg_sleep(), pg_sleep_until(), and pg_sleep_for() are frequently used functions that allow us to pause the query’s execution halfway. The pg_sleep_for() function is used to put larger delays between the query’s execution. This post has shown various methods to pause the query execution in PostgreSQL.