patternsqlMinor
Cross join table row with generate_series
Viewed 0 times
crosswithjoingenerate_seriesrowtable
Problem
Currently I have
Table definition
which gives me a cartesian product of the plans table and timestamp generated series.
I would like to introduce a specific duration for each plans row, so one row could have "1 hour", mean while the other have "30 minutes" and then generate each combination for every row. A CROSS JOIN here doesn't work, I tried:
SQL Fiddle: http://sqlfiddle.com/#!17/97a12/10
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
, timestamp '2021-01-07 22:00'
, interval '1 hour') t(x)Table definition
create table plans
(
id serial not null
constraint plans_pk
primary key,
name varchar default 50,
duration interval default '01:00:00'::interval
);which gives me a cartesian product of the plans table and timestamp generated series.
I would like to introduce a specific duration for each plans row, so one row could have "1 hour", mean while the other have "30 minutes" and then generate each combination for every row. A CROSS JOIN here doesn't work, I tried:
SELECT x, plans.name
FROM plans
CROSS JOIN generate_series(timestamp '2021-01-01 06:00'
, timestamp '2021-01-07 22:00'
, interval plans.duration) t(x)SQL Fiddle: http://sqlfiddle.com/#!17/97a12/10
Solution
Use a lateral join:
SELECT x, plans.name
FROM plans
CROSS JOIN LATERAL
generate_series(timestamp '2021-01-01 06:00',
timestamp '2021-01-07 22:00',
plans.duration::interval) t(x)Code Snippets
SELECT x, plans.name
FROM plans
CROSS JOIN LATERAL
generate_series(timestamp '2021-01-01 06:00',
timestamp '2021-01-07 22:00',
plans.duration::interval) t(x)Context
StackExchange Database Administrators Q#285817, answer score: 2
Revisions (0)
No revisions yet.