How to declare a variable in a PostgreSQL query

This solution is based on the one proposed by fei0x but it has the advantages that there is no need to join the value list of constants in the query and constants can be easily listed at the start of the query. It also works in recursive queries.

Basically, every constant is a single-value table declared in a WITH clause which can then be called anywhere in the remaining part of the query.

• Basic example with two constants:

WITH
    constant_1_str AS (VALUES ('Hello World')),
    constant_2_int AS (VALUES (100))
SELECT *
FROM some_table
WHERE table_column = (table constant_1_str)
LIMIT (table constant_2_int)

Alternatively you can use SELECT * FROM constant_name instead of TABLE constant_name which might not be valid for other query languages different to postgresql.

True, there is no vivid and unambiguous way to declare a single-value variable, what you can do is

with myVar as (select "any value really")

then, to get access to the value stored in this construction, you do

(select * from myVar)

for example

with var as (select 123)    
... where id = (select * from var)

You may resort to tool special features. Like for DBeaver own proprietary syntax:

@set name = 'me'
SELECT :name;
SELECT ${name};

DELETE FROM book b
WHERE b.author_id IN (SELECT a.id FROM author AS a WHERE a.name = :name);

Here is an example using PREPARE statements. You still can't use ?, but you can use $n notation:

PREPARE foo(integer) AS
    SELECT  *
    FROM    somewhere
    WHERE   something = $1;
EXECUTE foo(5);
DEALLOCATE foo;

In DBeaver you can use parameters in queries just like you can from code, so this will work:

SELECT *
FROM somewhere
WHERE something = :myvar

When you run the query DBeaver will ask you for the value for :myvar and run the query.

As you will have gathered from the other answers, PostgreSQL doesn’t have this mechanism in straight SQL, though you can now use an anonymous block. However, you can do something similar with a Common Table Expression (CTE):

WITH vars AS (
    SELECT 5 AS myvar
)
SELECT *
FROM somewhere,vars
WHERE something = vars.myvar;

You can, of cause have as many variables as you like, and they can also be derived. For example:

WITH vars AS (
    SELECT
        '1980-01-01'::date AS start,
        '1999-12-31'::date AS end,
        (SELECT avg(height) FROM customers) AS avg_height
)
SELECT *
FROM customers,vars
WHERE (dob BETWEEN vars.start AND vars.end) AND height<vars.avg_height;

The process is:

• Generate a one-row cte using SELECT without a table (in Oracle you will need to incllude FROM DUAL).
• CROSS JOIN the cte with the other table. Although there is a CROSS JOIN syntax, the older comma syntax is slightly more readable.
• Note that I have cast the dates to avoid possible issues in the SELECT clause. I used PostgreSQL’s shorter syntax, but you could have used the more formal CAST('1980-01-01' AS date) for cross-dialect compatibility.

Normally, you want to avoid cross joins, but since you’re only cross joining a single row, this has the effect of simply widening the table with the variable data.

In many cases, you don’t need to include the vars. prefix if the names don’t clash with the names in the other table. I include it here to make the point clear.

Also, you can go on to add more CTEs.

This also works in all current versions of MSSQL and MySQL, which do support variables, as well as SQLite which doesn’t, and Oracle which sort of does and sort of doesn’t.

Here is a code segment using plain variable in postges terminal. I have used it a few times. But need to figure a better way. Here I am working with string variable. Working with integer variable, you don't need the triple quote. Triple quote becomes single quote at query time; otherwise you got syntax error. There might be a way to eliminate the need of triple quote when working with string variables. Please update if you find a way to improve.

\set strainname '''B.1.1.7'''

select *
from covid19strain
where name = :strainname ;