0

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
4

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.

  • Thank you very much for the assist. It worked without issue. – Alex Willison Jan 27 '17 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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