Open nilmerg opened 1 year ago
You need an extra set of parenthesis around multiple column names:
noma_test=# SELECT COUNT(DISTINCT (object_id, source_id, tag)) FROM object_extra_tag;
count
-------
0
(1 row)
And why? Didn't find any explanation myself online.
I don't know, all I can say is that it works.
The syntax in the documentation actually reads like it should work without (but well, it doesn't):
aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
May be fixed by https://github.com/Icinga/ipl-orm/pull/108
This is an error caused by COUNT
function and not DISTINCT
. COUNT
function takes only one argument. But in case of MySQL when COUNT(DISTINCT )
is used, it allows for multiple columns to be specified after DISTINCT
. Check the MySQL documentation mentioning the same. However in standard SQL, they must be concatenated into one column which is done implicitly in case of Postgres when you surround the columns with parenthesis when using DISTINCT
.
See the below examples demonstrating the same using object_id_tag
table:
DISTINCT
of multiple column combination:
Postgres with parenthesis:
SELECT DISTINCT(tag, value) FROM object_id_tag LIMIT 10;
MySQL equivalent with CONCAT
:
SELECT DISTINCT(CONCAT('(', tag, ',', value, ')')) FROM object_id_tag limit 10;
Use of COUNT(DISTINCT )
:
Postgres:
SELECT COUNT(DISTINCT(tag, value) FROM object_id_tag;
MySQL:
SELECT COUNT(DISTINCT tag, value) FROM object_id_tag;
Outputs the same result as (in my case 982
):
SELECT COUNT(*) FROM (SELECT DISTINCT tag, value FROM object_id_tag) AS object_tag_value;
A query like this:
fails with this message: