sql - Postgresql: Gaps Between tsranges, empty set -

sql - Postgresql: Gaps Between tsranges, empty set -

i have tables of reservations each user:

reservations_development=# \d reservations table "public.reservations" column | type | modifiers ------------+---------+----------------------------------------------------------- id | integer | not null default nextval('reservations_id_seq'::regclass) user_id | integer | occurrence | tsrange | indexes: "reservations_pkey" primary key, btree (id) "reservations_occurrence_user_id_excl" exclude using gist (occurrence &&, user_id =)

i trying create view of gaps/opening between reservations each user, , have next query:

create or replace view reservation_gaps ( user_mins (select tsrange(localtimestamp, min(lower(occurrence))), user_id ( select user_id, occurrence reservations lower(occurrence) >= localtimestamp ) y grouping user_id ), gaps (select tsrange(upper(occurrence), lead(lower(occurrence),1, localtimestamp + interval '1 year') on (win_user_gaps)), user_id ( select user_id, occurrence reservations ) x window win_user_gaps (partition user_id order occurrence) union select * user_mins ) select * gaps order user_id, tsrange );

it gives expected results long user has 1 reservation, if user new, , has not been reserved empty result.

i need in way append {tsrange(localtimestamp, localtimestamp + interval '1 year'), user_id} row view each user without reservation, i'm stumped how that.

thanks

you should alter cte union all artificial rows , utilize distinct on select 1 row per user.

with user_mins (select distinct on (user_id) user_id, tsrange from( select tsrange(localtimestamp, min(lower(occurrence))) tsrange, user_id, 1 priotity ( select user_id, occurrence reservations lower(occurrence) >= localtimestamp ) y grouping user_id union select user_id, tsrange(localtimestamp, localtimestamp + interval '1 year'), 0 users) order user_id, priority desc )

sql database postgresql