ravi sankar

Posted on 18th July 2024|30 views


Postgresql Declare Variable

How to declare a variable in PostgreSQL?

1 answers
chris bons

Posted on 18th July 2024| views

  • We use a constant name and with a particular data type for a memory location in the PostgreSQL database.


variable_name data_type [:= expression]; 

  • Now when you run this, you can declare and assign the variable with a value.

For example:

first_name VARCHAR(50) := 'Richard';

last_name  VARCHAR(50) := 'Rambo';

payment    NUMERIC(11,2) := 150.5;

  • There are several data types which can be stored in this postgres as:
  1. Boolean, char, varchar, and text.
  2. Integer and floating-point number.
  3. Date, time, Timestamp, interval and serial or big serial.
  4. Particular types such as network address and geometric data.
  5. UUID for storing Universally Unique Identifiers
  6. Array for storing array strings, numbers, etc.
  7. JSON stores JSON data
  8. hstore stores key-value pair
  • Aliasing to variables:

Alias is used mostly for allotting a new name to a predetermined name.

This method changes the predetermined name to a new name for user convenience. 


newname ALIAS FOR oldname;

To change the old name to the new user-desired name, we use this method.

For example:

User_number ALIAS FOR UserID;

Hence this will update the user number to userid.

  • Copying data types:

This function mainly used for declaring variables that hold similar data type values.

Without knowing the data type also, we can declare the variable in the column as it stores the value in the form of that column data type.

%TYPE will declare the data type of the column to the new variable assigned.

%TYPE used for polymorphic functions which change their data type from one form to another every time you run a program. 


variable_name table_name.column_name%TYPE;

For example:

New_state_name states.name%TYPE := 'Hyderabad';


Write your answer


Get a Live FREE Demo

  • Explore the trending and niche courses and learning maps
  • Learn about tuition fee, payment plans, and scholarships
  • Get access to webinars and self-paced learning videos
Course *
Email *
Phone Number