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.

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;