16 May
2023
16 May
'23
11:42 a.m.
It can be a matter of db mode for usrloc, what it is
its value?
I'm currently using `db_mode 2`.
I just tested the command `kamcmd ul.db_users location` with `db_mode 1`, and I
encountered a different error in the log:
```
May 16 11:15:55 ip-10-0-1-63 /usr/sbin/kamailio[29873]: ERROR: db_postgres
[km_dbase.c:267]: db_postgres_submit_query(): 0x7f4a0d8e8148 PQsendQuery Error: ERROR:
function count(character varying, character varying) does not exist#012LINE 1: SELECT
COUNT(DISTINCT username, domain) FROM location WHERE ...#012 ^#012HINT: No
function matches the given name and argument types. You might need to add explicit type
casts.#012 Query: SELECT COUNT(DISTINCT username, domain) FROM location WHERE
(UNIX_TIMESTAMP(expires) = 0) OR (expires > NOW())
```
I believe this is another manifestation of the same issue with PostgreSQL and `usrloc`.
It seems that the problem lies in the SQL query:
```
SELECT COUNT(DISTINCT username, domain) FROM location;
```
After conducting some research, I discovered that PostgreSQL does not support the `count`
function with more than one column.
To achieve the same functionality, we could use the following query instead:
```
SELECT COUNT(DISTINCT CONCAT(username, '@', domain)) FROM location;
```
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3452#issuecomment-1549502954
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3452/1549502954(a)github.com>