On Tue, Aug 6, 2019 at 8:28 PM Paul Jungwirth <p...@illuminatedcomputing.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 ~{:-) > p...@illuminatedcomputing.com > -- Ibrar Ahmed
