Preamble
In this tutorial, you will learn how to declare variables in PostgreSQL using syntax and examples.
What is a variable in PostgreSQL?
In PostgreSQL, a variable allows a programmer to temporarily store data at runtime.
The syntax for declaring a variable in PostgreSQL
DECLARE variable_name [ CONSTANT ] datatype [ NOT NULL ] [ { DEFAULT |:= } initial_value ]
Parameters and arguments of variables
- variable_name – The name to be assigned to the variable.
- CONSTANT – Optional. If specified, the variable value cannot be changed after the initialization of the variable.
- datatype – The data type to be assigned to a variable.
- NOT NULL – Optional. If specified, the variable cannot contain the value of NULL.
- initial_value – Optional. This is the value originally assigned to a variable when it is declared. If no
- initial_value is specified, the variable is assigned a value of NULL.
Declaring a variable
Below is an example of a PostgreSQL declaration of a variable with vSite name.
DECLARE vSite varchar;
In this example, the variable vSite is declared as the varchar data type.
Then you can later set or change the value of the vSite variable as follows:
vSite:= 'Google.com';
This operator will set the vSite variable to ‘Google.com’.
Declaring a variable with an initial value (not a constant)
Below is an example of how to declare a variable in PostgreSQL and give it an initial value. This differs from the constant in that the variable value can be changed later.
DECLARE vSite varchar DEFAULT 'Google.com';
OR:
DECLARE vSite varchar:= 'Google.com';
This will declare the variable named vSite as varchar data type and assign the initial value to ‘Google.com’.
Later you can change the value of the vSite variable as follows:
vSite:= 'Bing.com';
This SET operator will change the value of the vSite variable from ‘Google.com’ to ‘Bing.com’.
Declaring a constant
Here is an example of how to declare a constant in PostgreSQL with the name vSiteID.
DECLARE vSiteID CONSTANT integer DEFAULT 50;
OR:
DECLARE vSiteID CONSTANT integer:= 50;
This will declare a constant named vSiteID as integer data type and assign an initial value of 50. Since this variable is declared using the CONSTANT keyword, you cannot change its value after initializing the variable.
Learn PostgreSQL Tutorial; Full Course for Beginners
About Enteros
Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Revolutionizing Cost Allocation and Attribution in Real Estate with Enteros: Optimizing Database Performance for Better Financial Insights
- 24 December 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros: Streamlining Root Cause Analysis and Shared Cost Optimization with Cloud FinOps in the Public Sector
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Database Performance with Enteros: Leveraging Cloud FinOps and Observability for the Financial Sector
- 23 December 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros: Enhancing Database Security Cost Management with RevOps and AIOps for the Insurance Sector
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…