sql - Is there a way to define a named constant in a PostgreSQL query?

Question

Is there a way to define a named constant in a PostgreSQL query? For example:

MY_ID = 5;
SELECT * FROM users WHERE id = MY_ID;

Answer 1

This question has been asked before (How do you use script variables in PostgreSQL?). However, there is a trick that I use for queries sometimes:

with const as (
    select 1 as val
)
select . . .
from const cross join
     <more tables>

That is, I define a CTE called const that has the constants defined there. I can then cross join this into my query, any number of times at any level. I have found this particularly useful when I'm dealing with dates, and need to handle date constants across many subqueries.