CODEWITHSUNDEEP
sqlpostgresqlpostgresql-8.4
Nandakumar V
Nandakumar V

Reputation: 4615

SQL multiple columns in single WHERE condition

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

Upvotes: 3

Views: 7257

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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
)

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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

Upvotes: 1

PaReeOhNos
PaReeOhNos

Reputation: 4398

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

Upvotes: 1

Related Questions