Re: SQL:2011 PERIODS vs Postgres Ranges?

On Tue, Aug 6, 2019 at 8:28 PM Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
wrote:

> Hi Ibrar,
>
> On 8/6/19 3:26 AM, Ibrar Ahmed wrote:
> > - Why we are not allowing any other datatype other than ranges in the
> > primary key. Without that there is no purpose of a primary key.
>
> A temporal primary key always has at least one ordinary column (of any
> type), so it is just a traditional primary key *plus* a PERIOD and/or
> range column to indicate when the record was true.
>
> > - Thinking about some special token to differentiate between normal
> > primary key and temporal primary key
>
> There is already some extra syntax. For the time part of a PK, you say
> `WITHOUT OVERLAPS`, like this:
>
> CONSTRAINT pk_on_t PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
>
> In this example `id` is an ordinary column, and `valid_at` is either a
> Postgres range or a SQL:2011 PERIOD. (The latter is not yet implemented
> in my patch but there are some placeholder comments.)
>
> Similarly a foreign key has one or more traditional columns *plus* a
> range/PERIOD. It needs to have a range/PERIOD on both sides. It too has
> some special syntax, but instead of `WITHOUT OVERLAPS` it is `PERIOD`.
> (Don't blame me, I didn't write the standard.... :-) So here is an example:
>
> CONSTRAINT fk_t2_to_t FOREIGN KEY (id, PERIOD valid_at)
> REFERENCES t (id, PERIOD valid_at)
>
> You should be able to see my changes to gram.y to support this new syntax.
>
> I hope this clears up how it works! I'm happy to answer more questions
> if you have any. Also if you want to read more:
>
> - This paper by Kulkarni & Michels is a 10-page overview of SQL:2011:
>
>
> https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf
>
> - This is a talk I gave at PGCon 2019 going over the concepts, with a
> lot of pictures. You can find text, slides, and a link to the video here:
>
> https://github.com/pjungwir/postgres-temporal-talk
>
> - This link is ostensibly an annotated bibliography but really tells a
> story about how the research has developed:
>
>
> https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/
>
> - There is also some discussion about PERIODs vs ranges upthread here,
> as well as here:
>
> https://www.postgresql-archive.org/Periods-td6022563.html
>
>
Thanks, Paul for the explanation. I think its good start, now I am looking
at the
range_agg patch to integrate that with that and test that.

> Yours,
>
> --
> Paul ~{:-)
> pj(at)illuminatedcomputing(dot)com
>

--
Ibrar Ahmed