Re: SQL:2011 PERIODS vs Postgres Ranges?

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

Yours,

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