How to use a postgres variable in the select clause

Question

With MSSQL it's easy, the @ marking the start of all variable names allows the parser to know that it is a variable not a column.

This is useful for things like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

declare @foo varchar(50) = 'bar';
select @foo;

How do you express this for postgres?

Answer 1

PostgreSQL isn't as flexible in where and how it allows usage of variables. The closest thing for what you're trying to accomplish likely would be surrounding it in a DO block like so:

DO $$
DECLARE foo TEXT;
BEGIN
 foo := 'bar' ;
 SELECT foo;
END $$;

Note this is context dependent, and you can find more information in this StackOverflow answer.

Additionally you can create a function that declares variables and returns a value like so:

CREATE FUNCTION example_function () RETURNS text AS '
 DECLARE
 
 -- Declare a constant integer with a
 -- default value of 5.
 five CONSTANT INTEGER := 5;
 
 -- Declare an integer with a default
 -- value of 100 that cannot be NULL.
 ten INTEGER NOT NULL := 10;
 
 -- Declare a character with
 -- a default value of "a".
 letter CHAR DEFAULT ''a'';
 
 BEGIN
 return letter;
 END;

More information on this approach here.

• user1822: This doesn't work, an anonymous DO block cannot return anything.
• J.D.: @a_horse_with_no_name I'm not a PostgreSQL expert so I could be wrong, but the answer I linked and referenced it from with 139 upvoted is also wrong?
• user1822: Well, that answer is wrong as well and I am surprised it got so many upvotes despite throwing the error mentioned in one of the comments: "ERROR: query has no destination for result data"
• J.D.: @a_horse_with_no_name Interesting, thanks. I'll look for an alternative to correct my answer.
• Peter Wone: Actually it does work for my intended use case. The select statement will be providing rows to an insert statement and in fact this answer leads directly to a useful outcome, so I'll accept it provided JD qualifies it with the extra info and mentions the use case. Also, I have since found that if I create a function around the code this can't return a value BS goes away.

Answer 2

SQL has no support for variables, this is only possible in procedural languages (in Postgres that would e.g. be PL/pgSQL).

The way to to this in plain SQL is to use a CTE (which is also a cross platform solution and not tied to any SQL dialect):

with vars (foo) as (
 values ('bar')
)
select foo 
from vars;

---

like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

Well you don't need a variable for that:

insert into target_table (c1, c2, c3)
select col_1, col_2, 'some value'
from staging_table;

• MikeB2019x: Does your second expression work if 'some value' is text? I think it just gets interpreted as a column, no?
• user1822: 'some value' is a text (string) value
• RAM237: In case if you still want to use a variable instead of 'some value' (e.g. to re-use it in multiple queries), you can put it like this: select col1, col2, :'my_var' from mytable; - note the single quotes, otherwise it will fail if the value is not alphanumeric word.