CODEWITHSUNDEEP
postgresqlcomparison
Pegus
Pegus

Reputation: 31

How to compare all fields in several rows in one table with result true or false (PostgreSQL)

I have such table (for example):

Field1 Field2 Field3 Field4 .....
1 a c c
1 a x c
1 a c c
2 a y j
2 b y k
2 b y l

I need to select by one field by one value and compare all fields in selected rows, like SELECT * WHERE Filed1=1.....COMPARE

I would like to have a result like:

Field1 Field2 Field3 Field4 .....
true true false true

Upvotes: 0

Views: 601

Answers (1)

Islingre
Islingre

Reputation: 2349

This should work for fixed columns and if there are no NULL values:

SELECT
    COUNT(DISTINCT t.col1) = 1,
    COUNT(DISTINCT t.col2) = 1,
    COUNT(DISTINCT t.col3) = 1,
    ...
FROM mytable t
WHERE t.filter_column = 'some_value'
GROUP BY col1;

If you have some nullable columns, perhaps you could give it a try with something like this instead of the COUNT(DISTINCT t.<colname>) = 1:

    BOOL_AND(NOT EXISTS(
        SELECT 1
        FROM mytable t2
        WHERE t2.filter_column = 'some_value'
            AND t2.<colname> IS DISTINCT FROM t.<colname>
    ))

If you do not have fixed columns, you should try to build up a dynamic query by a function taking as parameters the tablename, the name of the filter-column and the value for the filter.

Another remark: If you remove the filter (the condition t.filter_column = 'some_value') and add another output column as just t.filter_column, you should be able to recieve the result of this query for all distinct values in your filter-column.

Upvotes: 1

Related Questions