I have this table:
select * from db_table car city month_number Honda Boston 1 Honda Boston 2 Honda Boston 3 Honda Boston 4 Honda Boston 5 <---data ends for Honda and Boston Toyota Chicago 1 Toyota Chicago 2 Toyota Chicago 3 Toyota Chicago 4 Toyota Chicago 5 Toyota Chicago 6 <---data ends for Toyota and Chicago
I want to be able to self-generate data for each car and city for 24 months cycle, more like auto-increment.
What I am looking for:
car city month_number Honda Boston 1 Honda Boston 2 Honda Boston 3 Honda Boston 4 Honda Boston 5 Honda Boston 6 Honda Boston 7 Honda Boston 8 Honda Boston 9 Honda Boston 10 Honda Boston 11 Honda Boston 12 Honda Boston 13 Honda Boston 14 Honda Boston 15 Honda Boston 16 Honda Boston 17 Honda Boston 18 Honda Boston 19 Honda Boston 20 Honda Boston 21 Honda Boston 22 Honda Boston 23 Honda Boston 24 Toyota Chicago 1 Toyota Chicago 2 Toyota Chicago 3 Toyota Chicago 4 Toyota Chicago 5 Toyota Chicago 6 Toyota Chicago 7 Toyota Chicago 8 Toyota Chicago 9 Toyota Chicago 10 Toyota Chicago 11 Toyota Chicago 12 Toyota Chicago 13 Toyota Chicago 14 Toyota Chicago 15 Toyota Chicago 16 Toyota Chicago 17 Toyota Chicago 18 Toyota Chicago 19 Toyota Chicago 20 Toyota Chicago 21 Toyota Chicago 22 Toyota Chicago 23 Toyota Chicago 24
How can I do this in postgreSQL?
Advertisement
Answer
You can try to use generate_series with CROSS JOIN a subquery.
Query 1:
SELECT t1.car,
t1.city,
month_number
FROM generate_series(1, 24) month_number CROSS JOIN (
select DISTINCT car,
city
from db_table
) t1
ORDER BY t1.car,t1.city
| car | city | month_number | |--------|---------|--------------| | Honda | Boston | 1 | | Honda | Boston | 2 | | Honda | Boston | 3 | | Honda | Boston | 4 | | Honda | Boston | 5 | | Honda | Boston | 6 | | Honda | Boston | 7 | | Honda | Boston | 8 | | Honda | Boston | 9 | | Honda | Boston | 10 | | Honda | Boston | 11 | | Honda | Boston | 12 | | Honda | Boston | 13 | | Honda | Boston | 14 | | Honda | Boston | 15 | | Honda | Boston | 16 | | Honda | Boston | 17 | | Honda | Boston | 18 | | Honda | Boston | 19 | | Honda | Boston | 20 | | Honda | Boston | 21 | | Honda | Boston | 22 | | Honda | Boston | 23 | | Honda | Boston | 24 | | Toyota | Chicago | 1 | | Toyota | Chicago | 2 | | Toyota | Chicago | 3 | | Toyota | Chicago | 4 | | Toyota | Chicago | 5 | | Toyota | Chicago | 6 | | Toyota | Chicago | 7 | | Toyota | Chicago | 8 | | Toyota | Chicago | 9 | | Toyota | Chicago | 10 | | Toyota | Chicago | 11 | | Toyota | Chicago | 12 | | Toyota | Chicago | 13 | | Toyota | Chicago | 14 | | Toyota | Chicago | 15 | | Toyota | Chicago | 16 | | Toyota | Chicago | 17 | | Toyota | Chicago | 18 | | Toyota | Chicago | 19 | | Toyota | Chicago | 20 | | Toyota | Chicago | 21 | | Toyota | Chicago | 22 | | Toyota | Chicago | 23 | | Toyota | Chicago | 24 |