PostgreSQL PG_SLEEP() Function

info

The PG_SLEEP() function works the same across any PostgreSQL deployment. Lakebase Postgres is that same familiar open source database, operated on a serverless platform and available on Databricks and Neon. Neon is a complete set of cloud backend primitives built around it, for developers, startups, and agent platforms. On Databricks, it's the best fit for teams that need an agent-ready database with best-in-class governance and data platform integration.

Summary: in this tutorial, you will learn how to use the PostgreSQL PG_SLEEP() function to pause the execution of a query.

Introduction to the PostgreSQL PG_SLEEP() function

The PG_SLEEP() function allows you to create a delay (sleep) in your queries. The function can be useful when you want to test, simulate real-time processes, or add a pause between operations.

Here’s the syntax of the PG_SLEEP() function:

PG_SLEEP(seconds)

In this syntax, the seconds parameter specifies the number of seconds for which you want the execution to pause. It can be an integer or a decimal number with fractions.

The PostgreSQL PG_SLEEP() function examples

Let’s explore some examples of using the PG_SLEEP() function.

1) Basic usage of PG_SLEEP() function

The following example uses the PG_SLEEP() function to pause the execution for 3 seconds before returning any result:

SELECT pg_sleep(3);

After 3 seconds:

pg_sleep
----------

(1 row)

2) Using Fractional Seconds

The following example uses the PG_SLEEP() function to pause the execution for 1.5 seconds:

SELECT PG_SLEEP(1.5);

After 1.5 seconds:

pg_sleep
----------

(1 row)

3) Using the PG_SLEEP() function with NOW() function

The following example uses the PG_SLEEP() function between the NOW() functions:

SELECT NOW(), PG_SLEEP(3), NOW();

Output:

-[ RECORD 1 ]---------------------------
now      | 2024-03-21 02:26:37.710939-07
pg_sleep |
now      | 2024-03-21 02:26:37.710939-07

The output indicates that the result of the NOW() function does not change within the same statement even though we pause the execution between the calls of the NOW() functions for 3 seconds.

4) Using the PG_SLEEP() function with CLOCK_TIMESTAMP() function

The following example uses the PG_SLEEP() function between the CLOCK_TIMESTAMP() functions:

SELECT CLOCK_TIMESTAMP(), PG_SLEEP(3), CLOCK_TIMESTAMP();