SQL multiple columns in single WHERE condition

Question

I am trying to implement a query with multiple columns in a single where condition. But it making an sql error.
My original query is like this

SELECT id 
FROM usertable 
WHERE (user_addedon BETWEEN '2013-07-02 00:00:01' AND '2013-08-01 23:59:59')
   OR (user_deletedon BETWEEN '2013-07-02 00:00:01' AND '2013-08-01 23:59:59')
   OR (user_modified BETWEEN '2013-07-02 00:00:01' AND '2013-08-01 23:59:59')

What i am trying to do is to avoid the repetition of the BETWEEN. From another SO post SQL multiple columns in IN clause i created a query query like this

SELECT id 
FROM usertable 
WHERE (user_addedon,user_deletedon,user_modifiedon) BETWEEN '2013-07-02 00:00:01' AND '2013-08-01 23:59:59'

But it is showing an error ERROR: input of anonymous composite types is not implemented.
Is it because it cannot be implemented on PostgreSQL or is there any mistake in the query.
Also like to know if there are any other method to implement this?

I am using PostgreSQL 8.4

Answer 1

Whilst the other post shows you how to do an IN query with multiple columns, this does not indicate that it is possible with other operators such as BETWEEN.

As far as I know, the only way to achieve what you want it the way you show at the beginning of your question, with 3 BETWEEN statements :( Sucks I know but that's SQL

Answer 2

In case someone lands here looking for a solution that works in 9.2+, the date range type does it. This query returns true

select
    daterange'[2013-07-02, 2013-08-02)'
    @> any (array['2013-07-01', '2013-07-15', '2013-08-02']::date[])

The @> operator means "range contains element". The any operator works very much like in. The [ in the range means an inclusive bound while the ) means exclusive.

http://www.postgresql.org/docs/current/static/functions-range.html

http://www.postgresql.org/docs/current/static/functions-comparisons.html#AEN18030

http://www.postgresql.org/docs/current/static/rangetypes.html

Answer 3

Just as an exercise as I think the list of between is clearer

select id 
from usertable 
where exists (
    select 1
    from
        (values (user_addedon, user_deletedon, user_modified)) ud(ud)
        cross join
        (values ('2013-07-02'::date, '2013-08-02'::date)) r(ri, re)
    where ud >= ri and ud < re
)