Postgresql: the best way to check if column is equal to multiple values simultaneously

Question

If I want to check if a column is equal to one or other value, I write:

where col1 in (1, 4, 9);

But if I want to check if a column is equal to those values simultaneously, I should write:

where col1 = 1 and col1 = 4 and col1 = 9;

Is there in SQL any short form for this, like in?

Example:

c1  |  c2
----------- 
1   |  1
1   |  2
1   |  3

Select c1 from tablename where c2 = 1 and c2 = 2;

In result set I want 1

Ehh, a column can (at most) have only one *scalar* value for a row. — wildplasser, Aug 04 '16 at 18:51
There are arrays in PostgreSQL: https://www.postgresql.org/docs/9.0/static/arrays.html. But I'm not sure if it is what he is looking for (and I think it is not). If it is a array column you could do something like this: WHERE col1[1] = 4 and col1[2] = 9; — Pau, Aug 04 '16 at 18:54
One value for a row - yes, of course. But it may have many values for the entire column. — Stepan Pavlov, Aug 04 '16 at 19:02
@Pau Chorro, yes, I am aware of arrays and use them, but I think that their usage is rather slow... no, I have not array column. Example: c1 | c2 1 | 1 1 | 2 1 | 3 Select * from tablename there c2 = 1 and c2 = 2; — Stepan Pavlov, Aug 04 '16 at 19:04
In that case you should rephrase your question (like: "there exist rows for this column for all values in (1,4,9)" ) which comes *very close* to relational division. (but which rows do you want in the resultset?) — wildplasser, Aug 04 '16 at 19:05
@PauChorro Select distinct(c1) from tablename where c2 = 1 AND c2 = 2; — Stepan Pavlov, Aug 04 '16 at 19:38
Select distinct on(c1) from tablename col2 in (1, 4, 9) // -> postgresqltutorial.com/postgresql-select-distinct . Otherwhise: @Stepan Pavlov col2 = 1 and col2 = 2 your result will be 0 rows.... — Pau, Aug 04 '16 at 19:52
Here are similar questions: http://stackoverflow.com/questions/29117449/retrieve-multiple-rows-with-query-using-and-and-or http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation — Stepan Pavlov, Aug 06 '16 at 03:57

score 1 · Answer 1 · answered Aug 04 '16 at 19:08

1

PostgreSQL has extended the <operator> ALL syntay to arrays, so you can use:

WHERE col1 = ALL (ARRAY[1, 4, 9])

answered Aug 04 '16 at 19:08

Laurenz Albe

209,280
17
206
263

trying to get it work, but unsuccessfully: create table t1 ( c1 integer, c2 integer ); insert into t1 (c1, c2) values (1,1), (1, 2), (1, 3); select * from t1 WHERE c2 = ALL (ARRAY[1, 2]); – Stepan Pavlov Aug 06 '16 at 05:42
This clause is working opposite way. If we say select * from t1 WHERE c2 != ALL (array [1, 2]); it gives c2 = 3, because it compares our condition against every SINGLE value of our table. It can not compare the condition simultaneously against multiple rows. – Stepan Pavlov Aug 07 '16 at 05:16
1

What I gave you matches `where col1 = 1 and col1 = 4 and col1 = 9;` as you wrote. The example you gave is something different that I don't understand. Maybe if you explain in more detail what you really want? – Laurenz Albe Aug 07 '16 at 18:08

wildplasser · Accepted Answer · 2016-08-04T20:00:42.850

Relational division:

CREATE TABLE ztable
        ( c1 integer not NULL
        , c2 integer not null
        , PRIMARY KEY (c1,c2)
        );
INSERT INTO ztable(c1,c2) values
 (1   ,  1) -- [c1=1] should match
,(1   ,  2)
,(1   ,  3)
,(2   ,  3) -- [c1=2] should NOT match
,(2   ,  4)
        ;

WITH musthave (val) AS (VALUES (1), (2))
SELECT distinct c1
FROM ztable zt
WHERE NOT EXISTS ( -- we DONT WANT the records
        SELECT * FROM musthave mh
        WHERE NOT EXISTS ( -- for which one of the musthaves is missing
                SELECT * FROM ztable nx
                WHERE nx.c1 = zt.c1
                AND nx.c2 = mh.val
                )
        )
        ;

-- For smaller problems (limited set of musthave values) you could just do:

SELECT distinct c1
FROM ztable zt
WHERE zt.c2 = 1
AND EXISTS (
        SELECT *
        FROM ztable t2
        WHERE t2.c1 = zt.c1
        AND t2.c2 = 2
        );

-- and there is yet another method, which counts the matching c2 values:

SELECT DISTINCT zt.c1
FROM ztable zt
JOIN    (
        SELECT c1, COUNT(DISTINCT c2) AS cnt
        FROM ztable t2
        WHERE t2.c2 IN (1, 2)
        GROUP BY c1
        ) zzz ON zzz.c1= zt.c1
WHERE zzz.cnt = 2
    ;

Postgresql: the best way to check if column is equal to multiple values simultaneously

2 Answers2