Postgresql – Inserting date range arrays daterange[]

That is going to be horribly messy, since you'll need to find the single (staffid, effectivefrom) pair that's the newest prior to the dateParameter.

What I'd do is add another field effectiveTo to go with effectiveFrom, and then when you add a new target for a staff member, before you add it check and see if there are any current targets (effectiveTo IS NULL) and if there are then set their end date to the new target start date (optionally: throw an error if there's more than one current target).

So, the table turns out looking like:

 staffID | target | effectiveFrom | effectiveTo
 --------|--------|---------------|-------------
  1      |  6.0   | 2012-01-01    | 2012-03-01
  2      |  6.0   | 2012-01-01    | NULL
  3      |  6.0   | 2012-01-01    | NULL
  1      |  7.0   | 2012-03-01    | NULL

That makes your search query look like this:

SELECT  othertable.*, targets.target
FROM    othertable
            INNER JOIN 
        targets ON othertable.staffid = targets.staffid
WHERE dateParameter > effectiveFrom AND (dateParameter <= effectiveTo OR effectiveTo IS NULL)

The potential downside of that is data inconsistency (i.e. staff members having overlapping targets) - you can fairly easily write a script to check that though. I'd recommend making an addTarget(staffID, target, fromDate) stored procedure and wrapping all the checking/updating logic in that, also.

(A couple of style notes - I rewrote your query slightly to seperate the join condition from the where clause. Also, I know it's only an example, but SELECT table.* is a bad habit to get into - you land up transferring far more data than you need.)

You could define your own functions range_add/range_sub (intersection doesn't need special handling):

select range_add('[1,5)'::int4range, '[8,10)'::int4range);
     range_add      
--------------------
 {"[1,5)","[8,10)"}

select range_sub('[1,10)'::int4range, '[5,8)'::int4range);
     range_sub      
--------------------
 {"[1,5)","[8,10)"}

You could define them like this:

create or replace function range_add(int4range, int4range) returns int4range[] as
$$
    select
        case
            when $1 && $2
            then array[$1 + $2]
            else array[$1, $2]
        end
$$
language SQL;



create or replace function range_sub(int4range, int4range) returns int4range[] as
$$
    select
        case
            when $1 @> $2 and not isempty($2) and lower($1) <> lower($2) and upper($2) <> upper($1)
            then array[int4range(lower($1), lower($2), '[)'), int4range(upper($2), upper($1), '[)')]
            else array[$1 - $2]
        end
$$
language SQL;

Unfortunately, PostgreSQL is missing native support for this set-based behavior.