1

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
1
  • Hey, did you find a solution for this somewhere else?
    – Heits
    Mar 25 at 1:55
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.

1
  • 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
    Mar 2 '20 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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