Q:

Postgresql update a daterange column from 2 date columns

Let's say I have a table called baking_events and it has a start_date and end_date field. Now I've decided that I would like to take advantage of all of the nice range operators, so I add a new daterange column to the table called duration.
I would like to run a query to update duration on my baking_events table so that it is equal to [start_date, end_date] for each record before I delete the start_date and end_date columns.
Is there a way to do this in postgres without having to loop through each individual record? Something like: UPDATE TABLE baking_events SET duration = [start_date, end_date]::daterange (this query obviously does not work, but should give you an idea of what I want to do).

A:

Use the daterange constructor:
select daterange('2018-01-01', '2018-12-31', '[]');

        daterange        
-------------------------
 [2018-01-01,2019-01-01)
(1 row) 

Although '[]' is specified here, on display the value will be converted to canonical form, since daterange is a discrete range type.
The update command may look like this:
UPDATE baking_events 
SET duration = daterange(start_date, end_date, '[]');

Note, that after adding and updating the new column the table is not normalized, you should consider dropping the start_date and end_date columns.

