SQL SELECT that excludes rows with any of a list of values?

Question

I have found many Questions and Answers about a SELECT excluding rows with a value "NOT IN" a sub-query (such as this). But how to exclude a list of values rather than a sub-query?

I want to search for rows whose timestamp is within a range but exclude some specific date-times. In English, that would be:

Select all the ORDER rows recorded between noon and 2 PM today except for the ones of these times: Today 12:34, Today 12:55, and Today 13:05.

SQL might be something like:

SELECT * 
FROM order_
WHERE recorded_ >= ?
AND recorded_ < ?
AND recorded_ NOT IN ( list of date-times… )
;

So two parts to this Question:

• How to write the SQL to exclude rows having any of a list of values?
• How to set an arbitrary number of arguments to a PreparedStatement in JDBC?
  (the arbitrary number being the count of the list of values to be excluded)

Answer 1

Pass array

A fast and NULL-safe alternative would be a LEFT JOIN to an unnested array:

SELECT o.*
FROM   order_ o
LEFT  JOIN unnest(?::timestamp[]) x(recorded_) USING (recorded_)
WHERE  o.recorded_ >= ?
AND    o.recorded_ <  ?
AND   x.recorded_ IS NULL;

This way you can prepare a single statement and pass any number of timestamps as array.

The explicit cast ::timestamp[] is only necessary if you cannot type your parameters (like you can in prepared statements). The array is passed as single text (or timestamp[]) literal:

'{2015-07-09 12:34, 2015-07-09 12:55, 2015-07-09 13:05}', ...

Or put CURRENT_DATE into the query and pass times to add like outlined by @drake . More about adding a time / interval to a date:

• How to get the end of a day?

Pass individual values

You could also use a VALUES expression - or any other method to create an ad-hoc table of values.

SELECT o.*
FROM   order_ o
LEFT  JOIN (VALUES (?::timestamp), (?), (?) ) x(recorded_)
                                         USING (recorded_)
WHERE  o.recorded_ >= ?
AND    o.recorded_ <  ?
AND    x.recorded_ IS NULL;

And pass:

'2015-07-09 12:34', '2015-07-09 12:55', '2015-07-09 13:05', ...

This way you can only pass a predetermined number of timestamps.

Asides

For up to 100 parameters (or your setting of max_function_args), you could use a server-side function with a VARIADIC parameter:

• Return rows matching elements of input array in plpgsql function

I know that you are aware of timestamp characteristics, but for the general public: equality matches can be tricky for timestamps, since those can have up to 6 fractional digits for seconds and you need to match exactly.

Related

• Select rows which are not present in other table
• Optimizing a Postgres query with a large IN

Answer 2

SELECT * 
FROM order_
WHERE recorded_ BETWEEN (CURRENT_DATE + time '12:00' AND CURRENT_DATE + time '14:00')
      AND recorded_ NOT IN (CURRENT_DATE + time '12:34', 
                            CURRENT_DATE + time '12:55',
                            CURRENT_DATE + time '13:05')
 ;