Supporting temporal data with WITHOUT OVERLAPS and PERIOD

[roji]

Good end-to-end overview of the PG18 temporal features

Old/irrelevant pre-PG18 resources

• temporal_tables extension: https://github.com/arkhipov/temporal_tables/commits/master
• https://www.dbi-services.com/blog/temporal-tables-for-postgresql-15/
• https://wiki.postgresql.org/images/6/64/Fosdem20150130PostgresqlTemporal.pdf
• https://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/

[evman182]

Is this still on your radar for 7.0? Temporal tables is a possible solution for something I'm working on, and frankly, I'd love to avoid needing to justify the financial and development time costs of trying to migrate from PG to SQL Server

[roji]

It's unfortunately unlikely that I'll be able to do this for 7.0... One reason I'm a bit hesitant about doing this, is that it's unclear to what extent the above extension is up-to-date and the "final" temporal table support for PG; see notably this about possible movement for PG15. So it may be worth holding back on implementation until something more official makes it in.

However, it's probably possible to implement some scenarios with the temporal extension even without EF support - you may not actually need EF Core itself to know about temporal tables. Table creation can be managed with raw SQL in migrations (to add the temporal component), and as long as EF Core itself is used to query up-to-date date only, it doesn't need to be aware of the temporality. In other words, if the idea is for the application to access up-to-date data and use the temporal table as a manually-accessed audit or log, then things should be possible without any EF Core support.

[evman182]

I was reading the email thread about the PG patch a few days ago. Unfortunately it doesn't seem to be anywhere near getting merged.

Unfortunately the use case for which I'm looking to use temporal tables requires accessing them as part of the regular functions of the application.

[roji]

@evman182 I understand, and I'm sorry there aren't better news on this at the moment. You should hopefully be able to get by with raw SQL in EF Core for the temporal syntax - remember you can use that for the basic temporal query and then compose other LINQ query operators on top.

[ps-nk]

@roji Hi, there is an new Version of the extension available https://github.com/arkhipov/temporal_tables

[roji]

@nk-cmd thanks. It's good to know that there's progress there, but I'm still reluctant to do the substantial work needed to support temporal tables on an unofficial extension etc.

[domagojmedo]

Is there any issue to track for this becoming an official feature of postgre?

[roji]

@domagojmedo are you referring to the temporal_tables extension linked to above? If so, I have no idea (and that isn't really related to this repo in any way).

[domagojmedo]

Yes, I understand it's not related to this repo. I was just following up on your comment that you won't do any work for unofficial extension (which is completely understandable).
I was just wondering if you maybe know if this feature is even in consideration to be implemented as official postgre feature

[BenjamynWilliams-IconGroup]

This hasn't seen an update in a few years, what's the update on this?