Ygthkb

我可不觉得 - agree or disagree?

Why do we divide Permutations to get to Combinations?

Sauna: Wood does not feel so hot

What does @ mean in a hostname in DNS configuration?

How to play songs that contain one guitar when we have two or more guitarists?

How do I write a maintainable, fast, compile-time bit-mask in C++?

Is there a way to pause a running process on Linux systems and resume later?

Can a planet be tidally unlocked?

Rudeness by being polite

Was the Soviet N1 really capable of sending 9.6 GB/s of telemetry?

How can changes in personality/values of a person who turned into a vampire be explained?

Why is Shelob considered evil?

Can "ee" appear in Latin?

Last Reboot commands don't agree

How should I ship cards?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Cryptic cross... with words

Is opening a file faster than reading variable content?

How do I handle a blinded enemy which wants to attack someone it's sure is there?

Can you wish for more wishes from an Efreeti bound to service via an Efreeti Bottle?

How can a kingdom keep the secret of a missing monarch from the public?

Identical projects by students at two different colleges: still plagiarism?

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

Was Opportunity's last message to Earth "My battery is low and it's getting dark"?

Time oriented design in Postgresql

I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into viewPostgreSQL/PostGIS: Query seems inordinately time-consumingPostgreSQL trigram GIST vs. GIN indexesHow can I upgrade PostgreSQL while using PgQ queue system?PostgreSQL GiST for compressed entry typeIs this an efficient design for a temporal table with composite keys?What is the correct method to pass range of dates to a SQL Server 2016 temporal table query where you need a set returned for each day?Left join multiple tables for millions of rows on multiple columnsTrying to setup Postgresql 9.5 with different data directoryPoor temporal table performance on older values

0

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.

postgresql temporal-tables

share|improve this question

asked 2 hours ago

madtynmadtyn

1012

New contributor

madtyn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

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.

postgresql temporal-tables

share|improve this question

asked 2 hours ago

madtynmadtyn

1012

New contributor

madtyn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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.

postgresql temporal-tables

share|improve this question

asked 2 hours ago

madtynmadtyn

1012

New contributor

madtyn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

CREATE TABLE travel_log (

 id serial PRIMARY KEY,

 name varchar(255),

 travel_range daterange,

 EXCLUDE USING gist (travel_range WITH &&)

); 

share|improve this question

asked 2 hours ago

madtynmadtyn

1012

New contributor

madtyn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 2 hours ago

madtynmadtyn

1012

New contributor

madtyn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 hours ago

madtynmadtyn

1012

New contributor

madtyn is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 hours ago

madtynmadtyn

1012

1 Answer
1

active

oldest

votes

2

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 of unique (id)

---

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01') 

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01') 

will return false as there is no overlap.

share|improve this answer

edited 53 mins ago

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

madtyn is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230392%2ftime-oriented-design-in-postgresql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

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 of unique (id)

---

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01') 

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01') 

will return false as there is no overlap.

share|improve this answer

edited 53 mins ago

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112

add a comment | 

2

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 of unique (id)

---

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01') 

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01') 

will return false as there is no overlap.

share|improve this answer

edited 53 mins ago

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112

add a comment | 

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 of unique (id)

---

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01') 

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01') 

will return false as there is no overlap.

share|improve this answer

edited 53 mins ago

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01') 

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01') 

share|improve this answer

edited 53 mins ago

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112

answered 1 hour ago

a_horse_with_no_namea_horse_with_no_name

40.1k776112