CODEWITHSUNDEEP
sqlpostgresql
Tim
Tim

Reputation: 7474

Creating tables on-the-fly

It is often convenient in PosgreSQL to create "tables" on the fly so to refer to them, e.g.

with

selected_ids as (
select 1 as id
)

select *
from someTable
where id = (select id from selected_ids)

Is it impossible to provide multiple values as id this way? I found this answer that suggests using values for similar problem, but I have problem with translating it to the example below.

I would like to write subqueries such as

select 1 as id
union
select 2 as id
union
select 7 as id

or

select 1 as id, 'dog' as animal
union
select 7 as id, 'cat' as animal

in more condensed way, without repeating myself.

Upvotes: 0

Views: 592

Answers (3)

sagi
sagi

Reputation: 40491

You should use union and IN statement like this:

with
selected_ids as (
select 1 as id
union
select 2 as id
union
select 3 as id
....
)
select *
from someTable
where id in (select id from selected_ids)

after reviewing wingedpanther's idea and looking for it, you can use his idea IF those id's are continuously like this:

with
selected_ids as (
SELECT * FROM generate_series(Start,End) --(1,10) for example
)
select *
from someTable
where id in (select id from selected_ids)

If they are not continuously , the only way you can do that is by storing those ID's in a different table(maybe you have it already and if not insert it)

And then:

select *
from someTable
where id in (select id from OtherTable)

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21953

You can pass id and animal field in WITH like this

with selected_ids(id,animal) as (
values (1,'dog'), (2,'cat'), (3,'elephant'),(4,'rat')--,..,.. etc
)
select *
from someTable
where id = any (select id from selected_ids)

Upvotes: 2

klin
klin

Reputation: 121889

You can use arguments in the query alias:

with selected_ids(id) as (
    values (1), (3), (5)
)
select *
from someTable
where id = any (select id from selected_ids)

You can also use join instead of a subquery, example:

create table some_table (id int, str text);
insert into some_table values
(1, 'alfa'),
(2, 'beta'),
(3, 'gamma');

with selected_ids(id) as (
    values (1), (2)
)
select *
from some_table
join selected_ids
using(id);

 id | str  
----+------
  1 | alfa
  2 | beta
(2 rows)

Upvotes: 2

Related Questions