PostgreSQL Constants PostgreSQL · Anonymous Block · Constants

PL/pgSQL Constants

Master PL/pgSQL constants — the CONSTANT keyword for compile-time-immutable values. Covers required initialization, error-on-reassign behavior, type matching (TIMESTAMP not TIME for clock_timestamp), and the now()/statement_timestamp()/clock_timestamp() distinction. Worked examples include VAT calculation and elapsed-time measurement.

What Constants Are

A constant is a named value declared once at the top of a PL/pgSQL block whose value can never change after declaration. Trying to assign a new value to a constant inside the body produces a compile-time error.

Constants exist for two main reasons:

Clarity. A magic number like 0.18 sprinkled through code is meaningless; vat_rate CONSTANT NUMERIC := 0.18 documents what it is.
Safety. Code can't accidentally overwrite a value that's supposed to stay fixed for the entire block. The compiler enforces it.

Syntax

constant_name CONSTANT data_type [NOT NULL] := initial_value;

Element	Purpose
`constant_name`	The identifier you'll use to reference the value.
`CONSTANT`	The keyword that marks this as a constant — required for compile-time enforcement.
`data_type`	Any PostgreSQL type — INTEGER, NUMERIC, TEXT, BOOLEAN, DATE, TIMESTAMP, etc.
`:= initial_value`	Required for constants — unlike variables, you can't declare a constant without giving it a value at the same time.

⚠️ A constant must be initialized at declaration. Skipping the := value part on a constant produces a compile error — the language won't let you have a "blank" constant whose value gets set later, because that would defeat the immutability guarantee.

Constants vs Variables

Feature	Variable	Constant
Initial value required at declaration	Optional (defaults to NULL)	Required
Can be reassigned in the body?	Yes	No — compile error
Can be NULL?	Yes (unless `NOT NULL`)	Whatever you initialized it to (typically not NULL)
Use case	Working values that change during the block	Fixed values: rates, limits, configuration

Examples

Example 1 — VAT Calculation

The classic use of a constant: a tax rate that should never change mid-calculation. The block computes VAT and the gross total for a given net price:

PL/pgSQL — VAT calculation

DO $$
DECLARE
    vat_rate    CONSTANT NUMERIC(4, 2) := 0.18;
    net_price   NUMERIC(10, 2)         := 1000.00;
    vat_amount  NUMERIC(10, 2);
    gross_price NUMERIC(10, 2);
BEGIN
    vat_amount  := net_price * vat_rate;
    gross_price := net_price + vat_amount;

    RAISE NOTICE 'Net price:   %', net_price;
    RAISE NOTICE 'VAT (% percent): %', vat_rate * 100, vat_amount;
    RAISE NOTICE 'Gross price: %', gross_price;
END $$;

OUTPUT

psql — vat calculated

postgres=#DO $$ ... vat calculation block ... $$;

NOTICE: Net price: 1000.00

NOTICE: VAT (18.00 percent): 180.00

NOTICE: Gross price: 1180.00

The vat_rate stays at 0.18 for the entire block — there's no way to accidentally change it from 0.18 to 0.20 partway through the calculation.

Example 2 — Constants Cannot Be Reassigned

Demonstrating the safety guarantee — try to assign a new value to a constant and the compiler refuses:

PL/pgSQL — error case

DO $$
DECLARE
    pi CONSTANT NUMERIC := 3.14159;
BEGIN
    pi := 3.14;        -- This line will not compile
    RAISE NOTICE 'pi = %', pi;
END $$;

OUTPUT

psql — error

postgres=#DO $$ ... pi := 3.14 ... $$;

ERROR: "pi" is declared CONSTANT

LINE 5: pi := 3.14;

The error fires at parse time — before any of the body code runs. That's the value of CONSTANT: bugs caught at compile, not at runtime.

Example 3 — Capturing Block Start Time

A common pattern: capture when a block started using a timestamp constant, then use it later for elapsed-time logging. Note the type — TIMESTAMP, not TIME:

PL/pgSQL — block start time

DO $$
DECLARE
    started_at  CONSTANT TIMESTAMP := clock_timestamp();
    finished_at TIMESTAMP;
    elapsed_ms  NUMERIC;
BEGIN
    -- Simulate some work
    PERFORM pg_sleep(0.25);

    finished_at := clock_timestamp();
    elapsed_ms  := EXTRACT(EPOCH FROM (finished_at - started_at)) * 1000;

    RAISE NOTICE 'Started:  %', started_at;
    RAISE NOTICE 'Finished: %', finished_at;
    RAISE NOTICE 'Elapsed:  % ms', round(elapsed_ms, 1);
END $$;

OUTPUT

psql — timing

postgres=#DO $$ ... timing block ... $$;

NOTICE: Started: 2026-04-22 14:22:01.234567

NOTICE: Finished: 2026-04-22 14:22:01.487112

NOTICE: Elapsed: 252.5 ms

⚠️ The original tutorial used started_at CONSTANT TIME := clock_timestamp() — but TIME stores only hours/minutes/seconds with no date, while clock_timestamp() returns TIMESTAMPTZ. The cast would lose the date entirely, breaking elapsed-time math that crosses midnight. Always pair timestamp-returning functions with TIMESTAMP (or TIMESTAMPTZ) declarations.

Three Time-Related Functions

Postgres exposes several "current time" functions that constants frequently use. Knowing which is which matters:

Function	Returns	Updates within a transaction?
`now()` / `CURRENT_TIMESTAMP`	Time the transaction started	No — same value for the whole transaction
`statement_timestamp()`	Time the current statement started	Yes — changes between statements
`clock_timestamp()`	Real-time wall clock — now	Yes — changes on every call within a single statement

For elapsed-time measurement (Example 3), clock_timestamp() is the only correct choice — the others would always show 0 elapsed because they don't advance during the block.

Best Practices

Use constants for true invariants — tax rates, mathematical constants, configuration limits. Don't make every variable a constant just because its value happens not to change in this block.
Name them descriptively. vat_rate beats v; max_login_attempts beats n.
Match the type to the value. A timestamp constant needs TIMESTAMP (not TIME); a percentage needs NUMERIC with appropriate precision (not INTEGER).
Consider session-level alternatives for values shared across many functions: a SET custom GUC parameter, a config table, or a function that returns the value. Constants live only inside one block.
Don't fake constants with regular variables. my_const NUMERIC := 5 looks the same in casual reading but offers no protection — the next developer can change it. CONSTANT documents intent and enforces it.

Conclusion

A constant is declared with name CONSTANT type := initial_value; — initialization is mandatory.
Constants cannot be reassigned in the body; the compiler refuses with ERROR: "name" is declared CONSTANT.
Use them for fixed values: rates, limits, configuration. They make code self-documenting and safer.
Match the type carefully — TIMESTAMP for timestamps, not TIME (which only stores hours/minutes/seconds without a date).
For shared values across many functions, consider config tables or GUC parameters; constants are scoped to a single block.