How to declare a variable in a PostgreSQL query

I accomplished the same goal by using a WITH clause, it's nowhere near as elegant but can do the same thing. Though for this example it's really overkill. I also don't particularly recommend this.

WITH myconstants (var1, var2) as (   values (5, 'foo'))SELECT *FROM somewhere, myconstantsWHERE something = var1   OR something_else = var2;

There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.

An exception is WITH () query which can work as a variable, or even tuple of variables. It allows you to return a table of temporary values.

WITH master_user AS (    SELECT      login,      registration_date    FROM users    WHERE ...)SELECT *FROM usersWHERE master_login = (SELECT login                      FROM master_user)      AND (SELECT registration_date           FROM master_user) > ...;

You could also try this in PLPGSQL:

DO $$DECLARE myvar integer;BEGIN    SELECT 5 INTO myvar;    DROP TABLE IF EXISTS tmp_table;    CREATE TABLE tmp_table AS    SELECT * FROM yourtable WHERE   id = myvar;END $$;SELECT * FROM tmp_table;

The above requires Postgres 9.0 or later.