CODEWITHSUNDEEP
postgresqllogical-operators
taper
taper

Reputation: 9824

PostgreSQL XOR - How to check if only 1 column is filled in?

How can I simulate a XOR function in PostgreSQL? Or, at least, I think this is a XOR-kind-of situation.

Lets say the data is as follows:

id | col1 | col2 | col3
---+------+------+------
1  | 1    |      | 4
2  |      | 5    | 4
3  |      | 8    | 
4  | 12   | 5    | 4
5  |      |      | 4
6  | 1    |      | 
7  |      | 12   |

And I want to return 1 column for those rows where only one of the columns is filled in. (ignore col3 for now..

Lets start with this example of 2 columns:

SELECT
    id, COALESCE(col1, col2) AS col
FROM
    my_table
WHERE 
    COALESCE(col1, col2) IS NOT NULL -- at least 1 is filled in
AND
    (col1 IS NULL OR col2 IS NULL) -- at least 1 is empty
;

This works nicely an should result in:

id | col
---+----
1  | 1  
3  | 8   
6  | 1  
7  | 12

But now, I would like to include col3 in a similar way. Like this:

id | col
---+----
1  | 1  
3  | 8 
5  | 4  
6  | 1  
7  | 12

How can this be done is a more generic way? Does Postgres support such a method?

I'm not able to find anything like it.

Upvotes: 1

Views: 4477

Answers (3)

Jasen
Jasen

Reputation: 12422

rows with exactly 1 column filled in:

select * from my_table where
   (col1 is not null)::integer 
   +(col1 is not null)::integer 
   +(col1 is not null)::integer 
   =1

rows with 1 or 2 

select * from my_table where
   (col1 is not null)::integer 
   +(col1 is not null)::integer 
   +(col1 is not null)::integer 
   between 1 and 2

Upvotes: 6

Emery Lapinski
Emery Lapinski

Reputation: 1662

How about

select coalesce(col1, col2, col3)
from my_table
where array_length(array_remove(array[col1, col2, col3], null), 1) = 1

Upvotes: 0

Cesar
Cesar

Reputation: 498

The "case" statement might be your friend here, the "min" aggregated function doesn't affect the result.

select id, min(coalesce(col1,col2,col3))
from my_table
group by 1
having sum(case when col1 is null then 0 else 1 end+
           case when col2 is null then 0 else 1 end+
           case when col3 is null then 0 else 1 end)=1

[Edit] Well, i found a better answer without using aggregated functions, it's still based on the use of "case" but i think is more simple.

select id, coalesce(col1,col2,col3)
from my_table
where (case when col1 is null then 0 else 1 end+
       case when col2 is null then 0 else 1 end+
       case when col3 is null then 0 else 1 end)=1

Upvotes: 1

Related Questions