PostgreSQL – Constants
Unlike variables, the value of constants cannot be changed once initialized. The main purpose of the use of constants in PostgreSQL are:
- It makes the query more readable.
- It reduces the maintenance efforts.
Syntax: constant_name CONSTANT data_type := expression;
Let’s analyze the above syntax:
- First, specify the constant name. By convention, it is generally in the uppercase form.
- Second, put the CONSTANT keyword and specify the data type that the constant is associated with.
- Third, initialize a value for the constant.
The following example declares a constant named VAT for valued added tax and calculates the selling price from the net price:
DO $$ DECLARE VAT CONSTANT NUMERIC := 0.1; net_price NUMERIC := 20.5; BEGIN RAISE NOTICE 'The selling price is %', net_price * ( 1 + VAT ); END $$;
Now let’s attempt to change the constant as below:
DO $$ DECLARE VAT constant NUMERIC := 0.1; net_price NUMERIC := 20.5; BEGIN RAISE NOTICE 'The selling price is %', net_price * ( 1 + VAT ); VAT := 0.05; END $$;
As expected it raises an error as shown below:
It is important to note that PostgreSQL evaluates the value for the constant when the block is entered at run-time, not compile-time as shown in the below example:
DO $$ DECLARE start_at CONSTANT time := now(); BEGIN RAISE NOTICE 'Start executing block at %', start_at; END $$;
PostgreSQL evaluates the NOW( ) function every time we call the block. To prove it, we execute the block again:
And got a different result.