PostgreSQL is a relational database that facilitates its users with different built-in functions and operators. One such built-in function is “pg_sleep()” which is used to delay a query for a specified time period. It suspends the execution of the current process for a specific number of seconds. Once the specified seconds have passed, the process execution resumes automatically.

This article demonstrates how to use the pg_sleep() function in PostgreSQL using appropriate examples.

How to Use the pg_sleep() Function in Postgres?

pg_sleep() function accepts the fractional seconds as arguments and delays the execution until the specified seconds pass. Here is the basic syntax of the stated function:

pg_sleep(time);

Where the “time” represents the seconds that determine how long a process will sleep.

Example 1: How Does pg_sleep() Work in Postgres?

In the following example, the pg_sleep() function is used between two CLOCK_TIMESTAMP() functions:

SELECT CLOCK_TIMESTAMP(),
pg_sleep(10),
CLOCK_TIMESTAMP();

Ten seconds are specified in the pg_sleep() function to halt the execution for ten seconds:

The output clearly shows that there is a ten seconds delay between the execution of two CLOCK_TIMESTAMP() functions.

Example 2: Passing Negative Seconds to pg_sleep()

Let’s learn how the pg_sleep() function deals with the negative seconds:

SELECT CLOCK_TIMESTAMP(),
pg_sleep(-10),
CLOCK_TIMESTAMP();

The output shows that passing the negative seconds to the pg_sleep() function doesn’t make any difference.

Example 3: Passing Fractional Seconds to pg_sleep()

The fractional seconds can also be passed to the pg_sleep() function to pause the execution for a specific time:

SELECT CLOCK_TIMESTAMP(),
pg_sleep(1.5),
CLOCK_TIMESTAMP(),
pg_sleep(1.5),
CLOCK_TIMESTAMP();

This is how the pg_sleep() function works in PostgreSQL.

Conclusion

In PostgreSQL, the pg_sleep() function accepts the fractional seconds as arguments and delays the execution until the specified seconds pass. The pg_sleep() function suspends the execution of the current process for the specified number of seconds. Once the specified seconds have passed, the process execution resumes automatically. This article has explained different use cases of the Postgres pg_sleep() function.