HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Cross join table row with generate_series

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
crosswithjoingenerate_seriesrowtable

Problem

Currently I have

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.