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@illuminatedcomputing.com