npgsql
Look into supporting temporal data
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/
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
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.
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.
@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.
@roji Hi, there is an new Version of the extension available https://github.com/arkhipov/temporal_tables
@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.
Is there any issue to track for this becoming an official feature of postgre?
@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).
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
This hasn't seen an update in a few years, what's the update on this?
@BenjamynWilliams-IconGroup the situation hasn't really changed on the PG side - AFAIK there's no official temporal tables feature; see the comments above, they're all still valid.
This hasn't seen an update in a few years, what's the update on this?
maybe you can use interceptors and write your own layer above ef itself and spin a db agnostic library, (this is more of rant for myself as i am in the same boat need temporal data for a different db from mssql) .
Everyone, note that PG has great support for timestamp and date ranges (which other databases don't). This makes it pretty easy to have e.g. a daterange column expressing when the row was "valid", and then efficiently filtering by it. That can be seen as a sort of temporal table feature, without the special syntax like e.g. in SQL Server.
I actually think the PG way of supporting range types properly and allowing you to build stuff on top of them (temporal tables or otherwise) is a much better approach than e.g. the SQL Server feature.
But of course that doesn't really help people trying to migrate from SQL Server etc..