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 
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

1 Answer 1


You can do this with one EXISTS subquery:

UPDATE "index" AS i 
SET pln = TRUE
      ( 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.

  • Thank you very much for the assist. It worked without issue. Jan 27, 2017 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.