I have this table:
x
select * from db_tablecar city month_numberHonda Boston 1Honda Boston 2Honda Boston 3Honda Boston 4Honda Boston 5 <---data ends for Honda and BostonToyota Chicago 1Toyota Chicago 2Toyota Chicago 3Toyota Chicago 4Toyota Chicago 5Toyota Chicago 6 <---data ends for Toyota and ChicagoI 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_numberHonda Boston 1Honda Boston 2Honda Boston 3Honda Boston 4Honda Boston 5Honda Boston 6Honda Boston 7Honda Boston 8Honda Boston 9Honda Boston 10Honda Boston 11Honda Boston 12Honda Boston 13Honda Boston 14Honda Boston 15Honda Boston 16Honda Boston 17Honda Boston 18Honda Boston 19Honda Boston 20Honda Boston 21Honda Boston 22Honda Boston 23Honda Boston 24Toyota Chicago 1Toyota Chicago 2Toyota Chicago 3Toyota Chicago 4Toyota Chicago 5Toyota Chicago 6Toyota Chicago 7Toyota Chicago 8Toyota Chicago 9Toyota Chicago 10Toyota Chicago 11Toyota Chicago 12Toyota Chicago 13Toyota Chicago 14Toyota Chicago 15Toyota Chicago 16Toyota Chicago 17Toyota Chicago 18Toyota Chicago 19Toyota Chicago 20Toyota Chicago 21Toyota Chicago 22Toyota Chicago 23Toyota Chicago 24How 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_numberFROM generate_series(1, 24) month_number CROSS JOIN ( select DISTINCT car, city from db_table) t1ORDER 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 |