In PostgreSQL, constants are similar to variables but with a crucial difference: once their value is initialized, it cannot be altered. This immutability is beneficial for several reasons, enhancing both, the readability of SQL queries and reducing maintenance efforts.
Here we will look into the syntax, usage, and practical examples of constants in PostgreSQL to provide a clear understanding of their importance and operation.
Syntax
constant_name CONSTANT data_type := expression;
Let's analyze the above syntax:
- Constant Name: Typically written in uppercase letters, this follows the naming conventions similar to variables but is used to signify unchangeable values.
- CONSTANT Keyword: This explicitly declares the identifier as a constant.
- Data Type: Specifies the data type the constant holds, such as 'NUMERIC', 'VARCHAR', etc.
- Initialization: A constant must be initialized with a value at the time of declaration.
PostgreSQL Constants Examples
Let us take a look at some of the examples of Constants in PostgreSQL to better understand the concept.
Example 1: Calculating with Constants
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 $$;
Output:

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:

Explanation: This example clearly demonstrates how constants can simplify calculations in financial operations by keeping the tax rate fixed and visible.
Example 2: Time-Dependent Constants
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 $$;
Output:

PostgreSQL evaluates the NOW() function every time we call the block. To prove it, we execute the block again:

Output: Each execution of this block will capture and display the time at which the block starts running, showing how constants can dynamically initialize based on the current state when the block is entered.
Important Points About PostgreSQL Constants
- Using constants can improve the performance of queries by allowing PostgreSQL's query planner to make optimizations based on the immutability of values.
- In complex procedures, using constants can simplify debugging and future maintenance. Since the value is set in one place, any changes required later need only be made once, reducing the risk of errors or inconsistencies.
- Unlike some other programming environments where constants must be defined with static values, PostgreSQL allows constants to be initialized dynamically using expressions or functions that are evaluated at run-time.
- Any attempt to reassign a value to a constant after its initial definition will result in a compilation error.
Similar Reads
PostgreSQL Tutorial In this PostgreSQL tutorial youâll learn the basic data types(Boolean, char, text, time, int etc.), Querying and Filtering techniques like select, where, in, order by, etc. managing and modifying the tables in PostgreSQL. Weâll cover all the basic to advance concepts of PostgreSQL in this tutorial.
8 min read
PostgreSQL DATEDIFF Function PostgreSQL doesnât have a DATEDIFF function like some other databases, but you can still calculate the difference between dates using simple subtraction. This approach allows you to find out how many days, months, or years separate two dates. In this article, we'll explore how to compute date differ
6 min read
PostgreSQL - Data Types PostgreSQL is a powerful, open-source relational database management system that supports a wide variety of data types. These data types are essential for defining the nature of the data stored in a database column. which allows developers to define, store, and manipulate data in a way that aligns w
5 min read
PostgreSQL - Psql commands PostgreSQL, or Postgres, is an object-relational database management system that utilizes the SQL language. PSQL is a powerful interactive terminal for working with the PostgreSQL database. It enables users to execute queries efficiently and manage databases effectively.Here, we highlight some of th
2 min read
Top 50 PostgreSQL Interview Questions and Answers Are you preparing for a PostgreSQL interview? PostgreSQL is a powerful open-source relational database management system (RDBMS) that is well-known for its reliability, scalability, and rich set of features. Itâs a favorite among developers and businesses alike, making it essential to master if we w
15+ min read
How to Dump and Restore PostgreSQL Database? PostgreSQL remains among the most efficient and widely applied open-source relational database management systems. It provides the superior function of saving, configuring, and extracting information most effectively. In the process of migrating data, creating backups, or transferring databases betw
6 min read
PostgreSQL - Create Database Creating a database in PostgreSQL is an important task for developers and database administrators to manage data effectively. PostgreSQL provides multiple ways to create a database, catering to different user preferences, whether through the command-line interface or using a graphical interface like
5 min read
PostgreSQL - SERIAL When working with PostgreSQL, we need to create tables with unique primary keys. PostgreSQL offers a powerful feature known as the SERIAL pseudo-type which simplifies generating auto-incrementing sequences for columns. In this article, weâll learn about the PostgreSQL SERIAL pseudo-type by explain h
5 min read
PostgreSQL Connection String A connection string is an essential component that enables applications to communicate with databases or other data sources by providing the necessary configuration details. It consolidates critical information such as the server address, database name, user credentials, and additional parameters li
4 min read
PostgreSQL - IF Statement PostgreSQL IF statement is an essential tool for implementing conditional logic within SQL queries and stored procedures. It allows developers to execute different actions based on specific conditions and enhances the flexibility of database operations. In this article, we will explore various Postg
5 min read