Re:Why Use Postgres?
by tmaly on Sunday April 30, 2017 @05:25PM
(#14233931)
I have known about these for quite some time, but my only issue is that not all drivers in a particular language support range types.
[ Parent
| on HN ]
If your driver or ORM is insufficient, you can use views:
create table reservation (room text, duration tstzrange);
insert into reservation (room, duration) values
('A100', '[2017-04-30, 2017-05-07]'),
('A100', '[2017-05-10, 2017-05-12]');
create view my_orm_sucks (room, start, stop) as
select room, lower(duration), upper(duration) from reservation;
Let's pretend building A caught on fire, so we have to move to building FM: select * from my_orm_sucks;
room | start | stop
------+------------------------+------------------------
A100 | 2017-04-30 00:00:00-07 | 2017-05-07 00:00:00-07
A100 | 2017-05-10 00:00:00-07 | 2017-05-12 00:00:00-07
update my_orm_sucks set room = 'FM200' where room = 'A100';
The underlying table gets updated: select * from reservation;
room | duration
-------+-----------------------------------------------------
FM200 | ["2017-04-30 00:00:00-07","2017-05-07 00:00:00-07"]
FM200 | ["2017-05-10 00:00:00-07","2017-05-12 00:00:00-07"]
Note that updates involving the start and stop columns won't work with a view like that. You can use the rules system to get around that:https://www.postgresql.org/docs/current/static/sql-createrul...
https://www.postgresql.org/docs/current/static/rules-views.h...
Or better yet, fix the driver or ORM!
Thank you for that idea with views, I had not thought about that perspective. It will come in handy if I have to use ranges in the future. I do think ranges are a better way to model things.
Which language(s)?
not a single driver in go supports them
Well, that seems to be a recurring issue with go.
Not nearly as mature libs as Java.