How to use the IN clause forcing more columns into a tuple and then looking up that tuple among the-CodePudding

I have two Postgresql tables table_j and table_r containing some fields in common:

a, b, c, d

I would like to extract from table_r a tuple of these fields where a condition on other fields is met, and since this condition can be met for records that have the same values on fields a, b, c, d, I would like to extract unique tuples.

select distinct(a, b, c, d) from table_r where date > '2022-05-01' and code='123456'

I would like to extract from table_j records that match values of fields a, b, c, d from the previous query.

I have tried to do it like so:

select a, b, c, d, e 
from table_j 
where (a, b, c, d) in
    ( select distinct(a, b, c, d) from table_r where date > '2022-05-01' and code='123456' )
order by a, b, c, d;

but it tells

ERROR:  the subquery has too few columns

LINE 1: e from table_j where (a, b, c, d) in
                                          ^

So I bet this means that the results from the subquery is only one column, while the snippet

where (a, b, c, d) in

from the "outer" query is trating (a, b, c, d) as four columns, even if I have put them between parentheses.

So how can I turn those 4 columns in that snippet into only one column containing the tuple of the four fields?

CodePudding user response：

distinct is not a function and always applies to all columns of the SELECT list. Enclosing one or more columns in parentheses won't change the result of the DISTINCT.

Your problem stems from the fact that (a,b,c,d) create a single column with an anonymous record as its type in Postgres. It does not return four columns, but just a single one. And thus the comparison with four columns from the IN condition fails.

Additionally: in a sub-query for an IN condition the distinct is actually useless because the IN operator only checks the first match. It might even make things slower.

select a, b, c, d, e 
from table_j 
where (a, b, c, d) in (select distinct a, b, c, d 
                       from table_r 
                       where date > '2022-05-01' 
                         and code='123456' )
order by a, b, c, d;