CODEWITHSUNDEEP
sqlpostgresql
edog429
edog429

Reputation: 346

Is there a way to create a CTE with values in postgres?

I often just want to test out a function, or see what a variable might look like. This could be done by creating a temporary table, but I suspect that there is an easier way.

Basically,

WITH cte1 AS (
   SELECT 
      VALUES(1,2,3) AS temp_var1
    , VALUES(4,5,6) AS temp_var2
)
SELECT 
    temp_var1
  , temp_var2
  , (temp_var1 + temp_var2) AS temp_var3
FROM cte1

This would return

temp_var1 temp_var2 temp_var3
1 4 5
2 5 7
3 6 9

Darn she's fine.

Note I'm using PostgreSQL 9.2.15.

Upvotes: 22

Views: 10056

Answers (1)

user330315
user330315

Reputation:

I think you are looking for

WITH cte1 (temp_var1, temp_var2) AS (
  VALUES
   (1,4),
   (2,5),
   (3,6)
)
SELECT 
    temp_var1
  , temp_var2
  , (temp_var1 + temp_var2) AS temp_var3
FROM cte1

Upvotes: 45

Related Questions