PostgreSQL Variables (original) (raw)

Last Updated : 15 Jul, 2025

**PostgreSQL, one of the most powerful and advanced open-source relational database management systems, provides robust support for **procedural programming through its **PL/pgSQL language. A fundamental aspect of PL/pgSQL is the use of **variables that play a crucial role in storing temporary data and facilitating complex computations within database functions and procedures.

Let us better understand the Variables in **PostgreSQL to better understand the concept.

PostgreSQL Variables

• **PostgreSQL is an advanced open-source relational database management system (**RDBMS) known for its robustness, extensibility and support for complex data types.
• One of the powerful features of PostgreSQL is its ability to use variables within its procedural language, **PL/pgSQL.
• Variables in PostgreSQL allow developers to store temporary data for use within functions and stored procedures, enabling more dynamic and flexible queries and operations.

**Syntax

The following illustrates the syntax for declaring a variable:

variable_name data_type [:= expression];

Let's analyze the above syntax:

• **Variable Name: Specify the name of the variable. It is a good practice to assign a meaningful name to a variable. For example, instead of naming a variable "i" one should use **index or **counter.
• **Data Type: Associate a specific data type with the variable. The data type can be any valid PostgreSQL data type such as INTEGER, NUMERIC, VARCHAR, and CHAR.
• **Default Value: Optionally assign a default value to a variable. If you don’t, the initial value of the variable is initialized to **NULL.

Examples of PostgreSQL Variables

Let us take a look at some of the examples of Variables in PostgreSQL to better understand the concept.

**Example 1: Basic Variable Declaration and Usage

DO DECLAREcounterINTEGER:=1;firstnameVARCHAR(50):=′John′;lastnameVARCHAR(50):=′Doe′;paymentNUMERIC(11,2):=20.5;BEGINRAISENOTICE′ENDDECLARE
counter INTEGER := 1;
first_name VARCHAR(50) := 'John';
last_name VARCHAR(50) := 'Doe';
payment NUMERIC(11,2) := 20.5;
BEGIN
RAISE NOTICE '% % % has been paid % USD', counter, first_name, last_name, payment;
ENDDECLAREcounterINTEGER:=1;firstn​ameVARCHAR(50):=′John′;lastn​ameVARCHAR(50):=′Doe′;paymentNUMERIC(11,2):=20.5;BEGINRAISENOTICE′END;

**Output:

Explanation: In this example, we declared four variables: 'counter', 'first_name', 'last_name', and 'payment'. Each variable is initialized with a specific value. The '**RAISE NOTICE' **statement is used to display the values of these variables.

**Example 2: Using System Functions with Variables

DO DECLAREcreatedattime:=NOW();BEGINRAISENOTICE′PERFORMpgsleep(10);RAISENOTICE′ENDDECLARE
created_at time := NOW();
BEGIN
RAISE NOTICE '%', created_at;
PERFORM pg_sleep(10);
RAISE NOTICE '%', created_at;
ENDDECLAREcreateda​ttime:=NOW();BEGINRAISENOTICE′PERFORMpgs​leep(10);RAISENOTICE′END;

**Output:

*Explanation: In this example, we declared a variable '*created_at'**and initialized it with the current time using the NOW() *function. The '*pg_sleep(10)'**function pauses the execution for 10 seconds.

The 'RAISE NOTICE' statements display the value of '**created_at'**before and after the pause, showing that the time remains the same as it was initialized once.

Important Points About PostgreSQL Variables

• Variables declared within a block or function are only accessible within that block or function.
• Variables can be assigned values using the `:=` operator. Alternatively, you can utilize the `**SELECT INTO` statement to assign values from query results to variables.
• When assigning values to variables, it is essential to ensure that the data types are compatible. PostgreSQL will generate an error if there is a type mismatch.
• If a variable is not explicitly initialized, its default value is NULL.

Conclusion

In conclusion, **PostgreSQL variables are an integral part of developing efficient and dynamic database applications. Mastering the syntax for **declaring variables in PostgreSQL not only enhances the functionality of database procedures but also contributes to cleaner, more maintainable code by allowing developers to build robust applications that meet modern data processing needs.