Edson Horacio Junior Edson Horacio Junior - 4 months ago 10
SQL Question

select a "fake" column that is a range of specific numbers

I need to select table's a information along with a range of specific numbers, that is, for every a.id I want to show every number in the range, using PostgreSQL 8.4.

Let's suppose the range is the numbers 123, 175 and 192, this would be the result I want:

rangea.id

123   1    

175   1    

192   1    

123   2    

175   2    

192   2    

123   3    

175   3    

192   3    

I know I can achieve this using

select range, a.id
from a
inner join generate_series(1, 100, 1) range on true


But the thing is, I don't want to use
generate_series
because my range is random numbers, is there another way to do it?

Maybe something like this:

select range, a.id
from a
where range in (123, 175, 192)
group by range, a.id;

Answer

Given your comment:

For every a.id I want to show every number of the range

This creates what is called a cartesian product. Here's one generic option using a cross join with union all:

select a.id, r.rng
from a cross join (
  select 123 as rng 
  union all select 234
  union all select 556
  union all select 653
  union all select 634) r