CODEWITHSUNDEEP
postgresql
Aslan986
Aslan986

Reputation: 10324

Create a constant in Postgresql

Suppose that I have this query:

select * 
from myTable
where myTable.myCol in (1,2,3)

I would like to do that:

with allowed_values as (1,2,3)
select * 
from myTable
where myTable.myCol in allowed_values

It gives me a Syntax Error in the first row, can you help me fixing it?

Upvotes: 2

Views: 161

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656441

Close to what you probably had in mind:

WITH allowed_values AS (SELECT '{1,2,3}'::int[] AS arr)
SELECT * 
FROM   my_table
      ,allowed_values   -- cross join with a single row
WHERE  my_col = ANY (arr);

Better:

WITH allowed_values (my_col) AS (VALUES (1), (2), (3))
SELECT * 
FROM   allowed_values
JOIN   my_table USING (my_col)

But really, you can just simplify:

SELECT * 
FROM  (VALUES (1), (2), (3)) AS allowed_values (my_col)
JOIN   my_table USING (my_col);

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

The closest I can think to your syntax:

WITH allowed_values (id) AS 
  ( VALUES
      (1), (2), (3)
  )
SELECT * 
FROM myTable
WHERE id IN 
   (TABLE allowed_values) ;

Tested in SQL-Fiddle

Upvotes: 6

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

The simplest way forward is to correct your common table expression, then use it in a subselect.

with allowed_values as (
  select 1 id
  union all
  select 2
  union all
  select 3
)
select * from myTable
where myTable.id in (select id from allowed_values)

Upvotes: 2

a1ex07
a1ex07

Reputation: 37354

Try

with allowed_values as (select 1 as tst union all select 2 union all select 3)    
select * from myTable a
inner join c1 b ON (b.tst = a.myCol)

Upvotes: 2

Related Questions