Dionisis Kontominas schrieb am 26.07.2023 um 11:00:
> do not want two records to overlap, for the same user, the same role
> and also when the f_is_deleted is TRUE only.
> I do not care for the records when the f_is_deleted is FALSE on them; i.e. they should not be part of the restriction/constraint.
> How can I achieve this?
EXCLUDE USING gist (f_portal_user_id WITH =, f_portal_role_id WITH =, DATERANGE(f_start_date, f_end_date, '[]') WITH &&) where (f_is_deleted)
But that requires the btree_gist extension [1] extension, no?
Just confirming, because I'm been considering a similar approach for storing chunks of large files (> 1GB),
to enforce those chunks don't overlap, per-"file". Seems ideal to enforce no-overlap, but OTOH,
you can't seem to see how to enforce "no-holes" for chunks. One concern is the cost of adding that
enforcement of no-overlap. Most "files" will be small (a few bytes to a single digit MBs), while some
definitely go into multi-GB territory. So how well do exclusion constraints scale to 100K or 1M rows?
What's their time-complexity? In other words, should "smaller" (i.e. < 1MB) "files" go into a separate
table w/o an exclusion constraint and w/o chunking, while only the larger ones go to the chunked table?
Thanks, --DD