Variable declaration and assignment in PostgreSQL

Introduction

Variables let you store and reuse values inside your database code. This helps you keep things organized and avoid repeating yourself.

When you want to save a value to use multiple times in a query or function.

When you need to perform calculations step-by-step and keep intermediate results.

When writing stored procedures or functions that require temporary data storage.

When you want to make your SQL code easier to read by naming important values.

When you want to change a value during a process without affecting the original data.

Syntax

PostgreSQL

DECLARE variable_name data_type [DEFAULT initial_value];

-- To assign a value later inside a block:
variable_name := new_value;

Variables are declared inside PL/pgSQL blocks like functions or DO blocks.

Use := to assign or change the value of a variable after declaration.

Examples

This declares a variable named count as an integer and sets it to 0 initially.

PostgreSQL

DECLARE
  count INTEGER DEFAULT 0;

Declares a text variable name and assigns 'Alice' to it inside the block.

PostgreSQL

DECLARE
  name TEXT;
BEGIN
  name := 'Alice';
END;

Declares a numeric variable total and assigns a decimal value.

PostgreSQL

DECLARE
  total NUMERIC;
BEGIN
  total := 100.50;
END;

Sample Program

This code declares two variables, assigns a value to name, and prints a greeting message combining both variables.

PostgreSQL

DO $$
DECLARE
  greeting TEXT DEFAULT 'Hello';
  name TEXT;
BEGIN
  name := 'Bob';
  RAISE NOTICE '% %!', greeting, name;
END $$;

Important Notes

Variables only exist inside the block where they are declared.

You cannot use variables directly in plain SQL; they work inside PL/pgSQL blocks.

Use RAISE NOTICE to print variable values for debugging.

Summary

Variables store temporary values inside PL/pgSQL blocks.

Declare variables with DECLARE and assign values with :=.

Use variables to make your database code clearer and reusable.