I've been reading Snodgrass's "Developing Time-Oriented Database Applications in SQL" and I'm consulting other documents as well, but I'm willing to find more modern stuff about time-oriented design for databases and the tools available for that in Postgresql.
Also found this document.
I would be grateful just with any link or advice on the topic discussing:
- Temporal and bitemporal tables
- Coalescing
- Validity, applicability time, transaction time
- Primary keys, constraints and referential integrity in temporal DBs
- Design and good design practices for temporal DBs
- Native Postgresql solutions for all of the above
I have still more to read from the book and I've discovered some SQL:2011 issues about time, but they'are not implemented in Postgresql.
There's still some things I don't know how to handle or understand, for example, I found this article mentioning this:
CREATE TABLE travel_log (
id serial PRIMARY KEY,
name varchar(255),
travel_range daterange,
EXCLUDE USING gist (travel_range WITH &&)
);
which I understand it could be useful for not having overlapping rows, but even though I searched the Postgresql docs, I only read what the EXCLUDE does and that gist is just some type of INDEX, but I can't figure out which kind of syntax is the "WITH &&" thing and what does it provide.
I would be happy just having resources where I could learn about all this. Thank you very much.
Best Answer
The
WITH
keyword defines the operator which is used to test for "equality" between two rows.A regular unique index is just a special case of an exclusion constraint. If you were to define a "normal" unique constraint using an exclusion constraint, you would use
=
as the operator, e.g:exclude using btree (id with =)
instead ofunique (id)
The
&&
for range types is the "overlaps" operator which tests if two ranges, well, overlap.e.g.
returns true (as the ranges overlap)
while
will return false as there is no overlap.