PostgreSQL – Copying Data Types

PostgreSQL enables you to define a variable with a data type that references to the data type of a column of a table or the data type of another variable.

The below syntax refers to the data type of a column:

Syntax: variable_name table_name.column_name%TYPE;

The below syntax refers to the data type of another variable:

Syntax: variable_name variable%TYPE;

Example:

In this example, we will build a table (say, City) with a column (say, name) that has a CHAR data type and create a variable (say, city_name) and refer to the data type of the column as the data type of the variable.

city_name city.name%TYPE := 'Delhi';

Now let’s check the data type of the variable

SELECT pg_typeof(city_name);

This will establish that the variable is of type CHAR.

By using copying type feature, you receive the following advantages:

• First, you don’t need to care about the data type of the column. You declare a variable to just hold the values of that column in a query.
• Second, when the data type of the column changes, you don’t need to change the variable declaration in the function to adapt to the new changes.
• Third, you can refer to the type of variables to the data type of function arguments to create polymorphic functions since the type of internal variables can change from one call to the next.

Assigning aliases to variables:

PostgreSQL allows you to define an alias for any variable as follows:

new_name ALIAS FOR old_name;

The aliases are used mainly in a trigger procedure to assign more meaningful names for variables that have predetermined names e.g., NEW or OLD.