In PostgreSQL, variables are used to store the mutable(changeable) data whereas the constants store the immutable(unchangeable) data. Once a constant is initialized with a value, it stays the same in the entire program. Trying to modify the constant’s value will result in an error. Constants offer various features, such as easy code readability, reusability, maintainability, etc.

This post presents a comprehensive guide on Postgres constant along with suitable examples.

What are Constants in PostgreSQL?

In Postgres, constants are nothing but immutable or unchangeable variables. In PostgreSQL, there are three types of implicit constants: String Constants, Integer Constants, and Floating Point Constants. However, for more accuracy and efficiency, Postgres allows us to specify the Constants with explicit, user-defined types.

How to Declare a Constant in Postgres?

The declaration of a constant is the same as the variable’s declaration except for the “CONSTANT” keyword. The following syntax is used to declare a constant in Postgres:

DECLARE const_name CONSTANT data_type := value/expression;

In this syntax:

- DECLARE is a keyword that is used to declare a constant.
- The “const_name” represents any meaningful name of a constant.
- CONSTANT is a keyword that ensures its initial value doesn’t change throughout the program.
- Data_type can be any valid type, such as INT, TEXT, VARCHAR, etc.
- “:=” or “=” operators are used to initialize the constant.
- Replace the “value/expression” with a valid value or expression based on the specified data type.

Let’s consider the following examples to learn the usage of Postgres CONSTANTS practically.

Example 1: Declaring and Printing a Constant

The below snippet illustrates how to create and print a constant in Postgres:

DO $$ 
DECLARE
product_price CONSTANT INTEGER := 150;
BEGIN 
RAISE NOTICE 'The price of the selected product is %', product_price;
END $$;

- First, the “DO” keyword is used to execute the code block.
- Next, an integer constant is declared and initialized within the “DECLARE” block.
- The declared constant is printed in the “BEGIN” block using the “RAISE NOTICE” statement.

The output shows that the constant has been declared, initialized, and printed successfully.

Example 2: Variable is Declared Constant Error

In the following example, we tried to modify the initial value of the given constant:

DO $$
DECLARE
product_price CONSTANT INTEGER := 150;
BEGIN
product_price := 140;
RAISE NOTICE 'The price of the selected product is %', product_price;
END $$;

The output clearly states that a constant can’t be modified.

Example 3: Solving Constant Expression

In the following example, a variable and a constant are declared in the declaration section:

DO $$ 
DECLARE
original_price NUMERIC := 120.50;
discount CONSTANT NUMERIC := 10.0;
net_cost NUMERIC = original_price - discount;
BEGIN 
RAISE NOTICE 'The discounted price of the selected product is %', net_cost;
END $$;

The constant’s value is subtracted from the variable’s value and the resultant value is printed using the RAISE statement.

Example 4: Run Time Evaluation

PostgreSQL evaluates the constants at run-time, not at compile-time, just like it evaluates the default value of a variable:

DO $$ 
DECLARE
query_begin_at CONSTANT TIMESTAMP = CURRENT_TIMESTAMP;
BEGIN 
RAISE NOTICE 'Query begins at %', query_begin_at;
perform pg_sleep(10);
RAISE NOTICE 'Query begins at %', query_begin_at;
END $$;

In the above code:

- A constant named “query_begin_at” is declared with the TIMESTAMP data type.
- The “query_begin_at” is initialized with the CURRENT_TIMESTAMP.
- The value of the given constant is printed using the RAISE statement.
- The “pg_sleep()” function is used to put the “10” seconds delay between the execution of the first and the second raise statements.

The output shows that the pg_sleep() function doesn’t make any impact on the current timestamp. To see its impact, let’s invoke the block one more time:

The output proves that Postgres evaluates the CURRENT_TIMESTAMP function each time the block is invoked.

Conclusion

In Postgres, constants are nothing but immutable or unchangeable variables. Once a constant is initialized with a value, it stays the same throughout the program. Assigning a new value to a CONSTANT will result in an error stating "variable is declared constant". This post explained what a constant is, and how to declare and use it in PostgreSQL.