How to Declare a Variable in a PostgreSQL Query

A plain PostgreSQL SQL query does not declare a general-purpose variable. To use a value in a query, define it in a query-scoped WITH clause, declare a procedural variable inside a PL/pgSQL block, or pass it through a custom setting. These options have different scopes and lifetimes. In application code, prefer bound query parameters where your database driver supports them.

Declare a Variable in a PostgreSQL Query

The examples use a vehicle table with three rows. The SQL below is setup syntax for a PostgreSQL database. vehicle_name, vehicle_type, and vehicle_model are varchar columns, while vehicle_price is an integer.

Create the table with CREATE TABLE vehicle (vehicle_name varchar(30), vehicle_type varchar(30), vehicle_model varchar(30), vehicle_price integer, id serial UNIQUE NOT NULL PRIMARY KEY);, then add the sample data with INSERT INTO vehicle (vehicle_name, vehicle_type, vehicle_model, vehicle_price) VALUES ('Jaguar Range Rover Evoque', '4WD', '2019 Model', 7000000), ('Porsche Cayenne', '4WD', '2019 Model', 7000000), ('BMW 1 Series', '4WD', '2019 Model', 7000000);.

Run the setup once in the database where you will try the examples. Each query below filters this table for vehicles priced at 7000000.

For application code, bind the value through your PostgreSQL driver’s parameter API instead of inserting it into the SQL string. This Python snippet displays the SQL template and its separate parameter value. PostgreSQL drivers use different placeholder conventions, so use the form documented by your driver.

query = "SELECT vehicle_name FROM vehicle WHERE vehicle_price = %s;"
params = (7000000,)
print(query)
print(params)

SELECT vehicle_name FROM vehicle WHERE vehicle_price = %s;
(7000000,)

Use WITH Clause to Declare a Variable in a PostgreSQL Query

A WITH clause defines a common table expression (CTE) for one statement. Here, prices is a query-scoped relation containing the value to compare; it is not a general-purpose variable or a temporary table.

Use WITH prices AS (SELECT 7000000 AS car_price) SELECT vehicle_name FROM vehicle CROSS JOIN prices WHERE vehicle.vehicle_price = prices.car_price;.

With the sample rows above, this query returns the three vehicle names: Jaguar Range Rover Evoque, Porsche Cayenne, and BMW 1 Series. The CTE is available only to the statement that follows it, so define it again for another statement.

Use PostgreSQL Procedural Language to Declare a Variable in a PostgreSQL Query

A PL/pgSQL DO block can declare a variable and use it for procedural work. The variable price exists only within this block. This example writes matching rows to a temporary table, which lasts for the current database session by default and is dropped when that session ends.

Run the block DO $$ DECLARE price integer := 7000000; BEGIN CREATE TEMP TABLE expvehicles AS SELECT vehicle_name FROM vehicle WHERE vehicle_price = price; END $$;, then run SELECT vehicle_name FROM expvehicles; as a separate statement to read the result.

The CREATE TEMP TABLE statement is part of the block’s SQL work; the subsequent SELECT is separate. If you use CREATE TABLE instead, PostgreSQL creates a persistent table unless you explicitly choose a temporary table. Pick a distinct table name or remove an existing table before rerunning this setup, because a temporary table with the same name already in the session can prevent creation.

Use Dynamic Config Settings to Declare a Variable in a PostgreSQL Query

A custom setting can carry a value across statements on the same database session. Use SET to assign it, then current_setting to read it. Since current_setting returns text, cast the value to an integer before comparing it with vehicle_price.

Assign the value with SET my.vars.price = '7000000';, then query with SELECT vehicle_name FROM vehicle WHERE vehicle_price = current_setting('my.vars.price')::integer;.

The setting remains available for the session. To limit its value to the current transaction, use SET LOCAL my.vars.price = '7000000' after starting a transaction; the local value ends when that transaction commits or rolls back. For application queries, driver-bound parameters are generally easier to scope and safer than session settings, especially when connections are reused from a pool.