I'm using tsrange in my project, but I don't know how to represent something.

I have a product that can be displayed on my website at some times, and can be hidden at other times. But tsrange has only 2 bounds (lower, upper). How can I represent an suite of ranges which can be indexed (by gist) and selected with the same operators of tsrange? (like this: [(,"2019-01-01"],["2019-02-15","2019-03-16"],["2019-10-11","2019-12-20"]])

I tested tsrange[] but I can't index this and I can't use tsrange operators. I found that I can create them.

For this solution, I don't want a table product_range with two column's.

What is the best practice?


This is an example what I want :

  name VARCHAR(15),
  selling_dates TSRANGE[]
INSERT INTO product (name, selling_dates) VALUES ('Puppet', ARRAY[tsrange(NULL, '2019-01-01'), tsrange('2019-05-03', '2019-08-04')]);
CREATE INDEX product_selling_dates ON product USING GIST (selling_dates);

SELECT * FROM product WHERE selling_dates <@ '2019-06-10'::TIMESTAMP
  • Hey, did you find a solution for this somewhere else?
    – Heits
    Commented Mar 25, 2021 at 1:55

2 Answers 2


The best practice - and I'm afraid, the only approach that will make an indexed lookup possible - is the two-table approach; there is no index type that will help with a "timestamp falls within any item in a tsrange[] array" clause. The tsrange data must be in it's own column to benefit from a GIST index, and the index types that apply to arrays can only support exact-match for their "item-is-in-array" operators.

  • I see, but, related to LTREE, we can use operators on LTREE or LTREE[]. That I purpose wich was possible too. But I understand :)
    – Jimmy
    Commented Mar 2, 2020 at 7:23

It's a bit late, but with Postgres 14 you can make use of the new MultiRange types to achieve this.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.