How to determine if a timestamp in one table is between two timestamps in a subquery of another table?

Question

I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps.

Here are the schema of the two tables (edited for readability):

                 Table "public.index"
    Column     |           Type           | Modifiers 
---------------+--------------------------+-----------
 datetime      | timestamp with time zone | not null
 pln           | boolean                  | 

                     Table "public.planning"
      Column      |           Type           |         Modifiers          
------------------+--------------------------+----------------------------
 name             | text                     | 
 start_time       | timestamp with time zone | 
 end_time         | timestamp with time zone | 
 macro            | text                     | 
 state            | text                     | default 'deselected'::text

I would like to assign TRUE to the items in the "index" table if their 'datetime' timestamp falls between a 'start_time' and 'end_time' in a subquery of the "planning" table. Here is my attempt to do so:

UPDATE index 
SET pln=TRUE
WHERE datetime BETWEEN (SELECT start_time 
                        FROM planning 
                        WHERE state='selected' AND macro NOT LIKE 'mae%')
                   AND (SELECT end_time 
                        FROM planning 
                        WHERE state='selected' AND macro NOT LIKE 'mae%');

which results in the error:

ERROR:  more than one row returned by a subquery used as an expression

Answer 1

You can do this with one EXISTS subquery:

UPDATE "index" AS i 
SET pln = TRUE
WHERE EXISTS 
      ( SELECT *
        FROM planning AS p
        WHERE p.state = 'selected' 
          AND p.macro NOT LIKE 'mae%'
          AND p.start_time <= i.datetime 
          AND i.datetime < p.end_time 
      ) ;

I changed the BETWEEN to >= start_time and < end_time, as it is more common (and more sensible) to have inclusive-exclusive datetime ranges. You can change it back to >= and <= or to (the equivalent) BETWEEN.