PostgreSQL multiple range best practice

Question

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?

EDIT

This is an example what I want :

CREATE TEMPORARY TABLE product (
  id BIGSERIAL,
  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

Answer 1

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.

Answer 2

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