Assign same value to 2 columns in a select query

Question

Consider the following query

SELECT x + 2 AS d, x + 2 AS e FROM y;

Can I assign same value to d and e without repeating x + 2. Assume x + 2 is some complex operation.

I can achieve this using CTE but cannot use it.

I'm using postgres.

Answer 1

1. Put the equation in a lateral subquery and then re-use that in your select list: demo

   SELECT d, d as e 
   FROM y, LATERAL(SELECT x+2)AS subquery(d);
   

2. In psql you can also use \set d x+2:

   \set d x+2
   SELECT :d, :d as e 
   FROM y;
   

3. Wrap that equation in a SQL function and use that:

   create function d(x int)returns int return x+2;
   SELECT d(x), d(x) as e 
   FROM y;
   

4. If you don't want to use a CTE because you suspect it'll be re-evaluated multiple times for some reason, you can use materialized to make sure that doesn't happen.

   WITH cte AS MATERIALIZED(SELECT x+2 as d,* FROM y)
   SELECT d, d as e, *
   FROM cte;
   

Even if you repeat an expression over and over again, PostgreSQL can and will spot the repetition, evaluate it only once and re-use the result. While all of these could help clean up and shorten your code a bit, don't expect any performance gains compared to the repetitive version.