In PostgreSQL, a variable assigns a specific name to a memory location. Data can be temporarily stored in variables during code execution. In Postgres, variables need to be declared with a specific data type in the declaration block. Variables keep the mutable data that can be modified using a function or code block.

Try the new PgManage (Open Source) and get rid of PgAdmin!

This write-up presents a practical guide on declaring the variables in Postgres.

How to Declare a Variable in Postgres?

A variable in Postgres is always declared with a particular data type, such as INTEGER, TEXT, DATE, TIME, etc. Here is the syntax to declare a variable in Postgres:

DECLARE var_name < CONSTANT > data_type < NOT NULL > < { DEFAULT | := } expression >;

In this syntax:

- The “var_name” represents a meaningful name that will be assigned to a variable.
- “CONSTANT” is an optional parameter, used to assign a non-changeable value to the given variable.
- Replace the “data_type” with a valid data type, such as INT, DATE, TEXT, etc.
- The “NOT NULL” is an optional parameter that makes sure that the variable must contain a non-null value.
- The “DEFAULT” keyword initializes the given variable with a default or initial value.
- The “:=” or “=” operator is used to initialize a variable.

Let’s learn how to declare a variable in Postgres using the following examples.

Example 1: How to Declare and Initialize the Variables in Postgres?

The below code explains how to declare and initialize different variables in Postgres:

DO $$ 
DECLARE 
roll_number INT;
std_name TEXT; 
BEGIN
roll_number := 5;
std_name := 'Joseph';
RAISE NOTICE 'Student Roll No: %, Student Name is %', 
roll_number, 
std_name;
END $$;

In the above snippet:

- Initially, the “DO” keyword is used to execute the code block.
- Two variables “roll_number” and “std_name” are declared within the “DECLARE” block.
- The “BEGIN” keyword starts the transaction block.
- The declared variables are initialized with some values in the “BEGIN” block.
- The “RAISE NOTICE” statement is used to print the variables.
- The variables to be printed are specified within the “RAISE NOTICE” and “END” statements.
- The “%” placeholders are used to print the values of the given variables.
- The “END” statement halts the transaction block.

The output demonstrates that the variables have been successfully declared, initialized, and printed.

Example 2: How to Declare the Variables With Default Values in Postgres?

In the following coding example, various variables are declared and initialized with default string values:

DO $$ 
DECLARE 
std_name TEXT := 'Alex';
std_department VARCHAR(30) := 'Computer Science';
BEGIN
RAISE NOTICE '% is enrolled in % department', 
std_name, 
std_department;
END $$;

In this example:

- In the “DECLARE” block, two variables are declared and initialized with the default values.
- The “TEXT” and “VARCHAR” data types are used to declare two different variables.
- The “RAISE” command is used to display the errors or notices.

The output proves the variables’ declaration and initialization.

Example 3: How to Change the Variables Default Values in Postgres?

In Postgres, the variables' default or initial values can be changed in the program at any time:

DO $$ 
DECLARE 
std_name TEXT := 'Alex';
std_department VARCHAR(30) := 'Computer Science';
BEGIN
std_name = 'John';
RAISE NOTICE '% is enrolled in % department', 
std_name, 
std_department;
END $$;

In the above code, the initial value of the “std_name” variable is re-initialized in the “BEGIN” block:

The output shows that the value of “std_name” has been successfully changed from “Alex” to “John”.

Example 4: How to Declare Constant Variables in Postgres?

The following code snippet illustrates the usage of the CONSTANT keyword:

DO $$ 
DECLARE 
std_name CONSTANT TEXT := 'Alex';
std_department VARCHAR(30) := 'Computer Science';
BEGIN
std_name = 'John';
RAISE NOTICE '% is enrolled in % department', 
std_name, 
std_department;
END $$;

The CONSTANT is used to declare a non-changeable variable:

The output shows that an error occurs when we try to change the value of the constant variable.

Conclusion

In PostgreSQL, a variable is always declared with a particular data type, such as INTEGER, TEXT, DATE, TIME, etc. To declare a variable, use the “DECLARE var_name data_type:= expression;” syntax. Variables keep the mutable data that can be modified using a function or block code. However, the constant variables can be declared using the CONSTANT keyword. This write-up illustrated a thorough guide on how to declare a variable in Postgres using appropriate examples.