For ranges, have a look at the dedicated range types rather than adding two columns. This solves questions about whether they should be inclusive or not, provides many helper functions and operators and also allows to add an easy check constraint to ensure ranges are not overlapping.
Of course the information provided in the article is perfectly valid, but given that we are talking less known Postgres features, I thought I list one that’s related.
In general, I would recommend to always look at native data types if they are available because they will ensure correct data at the time of insertion and because you get to use all of the provided functions and operators to manipulate and query them.
Range types can be useful for the type of use case presented in the article (scheduling). PostgreSQL 14 even went further and added a Multirange Types that can potentially be used to represent the entire schedule.
However, there are a few downsides to using custom types. Two that I often consider:
ORM support
How easy would it be for non-developers to interact with these types (from reporting tools etc.).
I’ve only read the ToC but already love this post - so much value in spreading knowledge about all the capabilities of Postgres. I guess many people think of these as “advanced features”, but that’s just because the expectations of what a RDBMS is have been narrowed down for years.
For ranges, have a look at the dedicated range types rather than adding two columns. This solves questions about whether they should be inclusive or not, provides many helper functions and operators and also allows to add an easy check constraint to ensure ranges are not overlapping.
Of course the information provided in the article is perfectly valid, but given that we are talking less known Postgres features, I thought I list one that’s related.
In general, I would recommend to always look at native data types if they are available because they will ensure correct data at the time of insertion and because you get to use all of the provided functions and operators to manipulate and query them.
Range types can be useful for the type of use case presented in the article (scheduling). PostgreSQL 14 even went further and added a Multirange Types that can potentially be used to represent the entire schedule.
However, there are a few downsides to using custom types. Two that I often consider:
I’ve only read the ToC but already love this post - so much value in spreading knowledge about all the capabilities of Postgres. I guess many people think of these as “advanced features”, but that’s just because the expectations of what a RDBMS is have been narrowed down for years.