SqlExceptionHelper : ERROR: function count(character varying, integer) does not exist

Question

I have a spring application and I have a native query with this syntax:

select  
    COUNT(DISTINCT person.id,(CASE WHEN salary_person.rating = 'Satisfactory' THEN 1 END)) AS totalSatisfactory, 
    COUNT(DISTINCT person.id,(CASE WHEN salary_person.rating = 'Unsatisfactory' THEN 1 END)) AS totalUnsatisfactory
    from person
    join salary_person on person.id = salary_person.person_id;   

I get the error:

 ERROR: function count(character varying, integer) does not exist

As database I use PostgreSQL. I mention that in mysql, the query is working.

Answer 1

Postgres does not support count()with more than one column. You can however simply turn both columns into a single column of an anonymous record type, by using something like: (col_one, col_two) - that is a single column of an an anonymous record type.

select COUNT(DISTINCT (person.id,(CASE WHEN salary_person.rating = 'Satisfactory' THEN 1 END))) AS totalSatisfactory, 
       COUNT(DISTINCT (person.id,(CASE WHEN salary_person.rating = 'Unsatisfactory' THEN 1 END))) AS totalUnsatisfactory
from person
  join salary_person on person.id = salary_person.person_id;   

Note the parentheses around the two columns.

---

However, in Postgres you have a more elegant way to do what you want, by using conditional aggregation with the filter clause:

select COUNT(DISTINCT person.id) filter (where salary_person.rating = 'Satisfactory') AS totalSatisfactory, 
       COUNT(DISTINCT person.id) filter (where salary_person.rating = 'Unsatisfactory') AS totalUnsatisfactory
from person
  join salary_person on person.id = salary_person.person_id;   

Answer 2

A quick and dirty alternative to count distinct on multiple columns regardless of type is to just concat the two columns into a single column and count distinct that

SELECT
  COUNT(DISTINCT CONCAT(person.id, salary_person.rating))
FROM person
  JOIN salary_person on person.id = salary_person.person_id; 

However, based on what you want to do, can't you just count distinct by rating and filter for certain ratings?

SELECT
  salary_person.rating,
  COUNT(DISTINCT person.id)
FROM person
  JOIN salary_person on person.id = salary_person.person_id
WHERE 
  salary_person.rating in ('Satisfactory', 'Unsatisfactory')
GROUP BY 1;