ERROR: 42883: function date_format(date, unknown) does not exist

[n-rodriguez]

Bug

Url sollicitée: /compta/bank/graph.php?account=2&year=2022&month=7
Gestionnaire de menu: eldy_menu.php

Modules/Applications: user, propal, tax, banque, facture, fournisseur, service, societe
Type gestionnaire de base de données: pgsql
Requête dernier accès en base en erreur: SELECT date_format(b.datev,'%Y%m%d'), SUM(b.amount) FROM llx_bank as b, llx_bank_account as ba WHERE b.fk_account = ba.rowid AND ba.entity IN (1) AND b.datev >= '2022-07-01 00:00:00' AND b.datev < '2022-8-01 00:00:00' AND b.fk_account IN (2) GROUP BY date_format(b.datev,'%Y%m%d')
Code retour dernier accès en base en erreur: DB_ERROR_42883
Information sur le dernier accès en base en erreur: ERROR: 42883: function date_format(date, unknown) does not exist\nLINE 1: SELECT date_format(b.datev,'%Y%m%d'), SUM(b.amount) FROM llx...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\nLOCATION: ParseFuncOrColumn, parse_func.c:629

Environment Version

15.0.2

Environment OS

Docker : https://hub.docker.com/r/tuxgasy/dolibarr/

Environment Web server

Apache/2.4.38 (Debian)

Environment PHP

7.4.29

Environment Database

Postgres

[n-rodriguez]

The same error happens on /compta/bank/annuel.php?account=2 url :

Url sollicitée: /compta/bank/annuel.php?account=2
Gestionnaire de menu: eldy_menu.php

Modules/Applications: user, propal, tax, banque, facture, fournisseur, service, societe
Type gestionnaire de base de données: pgsql
Requête dernier accès en base en erreur: SELECT SUM(b.amount), date_format(b.dateo,'%Y-%m') as dm FROM llx_bank as b, llx_bank_account as ba WHERE b.fk_account = ba.rowid AND ba.entity IN (1) AND b.amount >= 0 AND b.fk_account IN (2) GROUP BY dm
Code retour dernier accès en base en erreur: DB_ERROR_42883
Information sur le dernier accès en base en erreur: ERROR: 42883: function date_format(date, unknown) does not exist\nLINE 1: SELECT SUM(b.amount), date_format(b.dateo,'%Y-%m') as dm FRO...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\nLOCATION: ParseFuncOrColumn, parse_func.c:629

[n-rodriguez]

The same error happens on /compta/facture/stats/index.php?leftmenu=customers_bills_stats :

Url sollicitée: /compta/facture/stats/index.php?leftmenu=customers_bills_stats
Gestionnaire de menu: eldy_menu.php

Modules/Applications: user, tax, banque, societe, propal, service, facture
Type gestionnaire de base de données: pgsql
Requête dernier accès en base en erreur: SELECT date_format(datef,'%m') as dm, AVG(f.total_ht) FROM llx_facture as f WHERE f.datef BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59' AND f.fk_statut >= 0 AND f.entity IN (1) AND (f.fk_statut <> 3 OR f.close_code <> 'replaced') AND f.type IN (0,1,2,3,5) GROUP BY dm ORDER BY dm DESC
Code retour dernier accès en base en erreur: DB_ERROR_42883
Information sur le dernier accès en base en erreur: ERROR: 42883: function date_format(date, unknown) does not exist\nLINE 1: SELECT date_format(datef,'%m') as dm, AVG(f.total_ht) FROM l...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\nLOCATION: ParseFuncOrColumn, parse_func.c:629

[n-rodriguez]

The same error happens on /compta/stats/index.php?leftmenu=report&modecompta=CREANCES-DETTES :

Url sollicitée: /compta/stats/index.php?leftmenu=report&modecompta=CREANCES-DETTES
Gestionnaire de menu: eldy_menu.php

Modules/Applications: user, tax, banque, societe, propal, service, facture, comptabilite
Type gestionnaire de base de données: pgsql
Requête dernier accès en base en erreur: SELECT date_format(f.datef,'%Y-%m') as dm, sum(f.total_ht) as amount, sum(f.total_ttc) as amount_ttc FROM llx_facture as f WHERE f.fk_statut in (1,2) AND f.type IN (0,1,2,3,5) AND f.entity IN (1) GROUP BY dm ORDER BY dm
Code retour dernier accès en base en erreur: DB_ERROR_42883
Information sur le dernier accès en base en erreur: ERROR: 42883: function date_format(date, unknown) does not exist\nLINE 1: SELECT date_format(f.datef,'%Y-%m') as dm, sum(f.total_ht) a...\n ^\nHINT: No function matches the given name and argument types. You might need to add explicit type casts.\nLOCATION: ParseFuncOrColumn, parse_func.c:629

[n-rodriguez]

Note: These errors happen after migrating from MariaDB to Postgres using https://github.com/dimitri/pgloader

[n-rodriguez]

Everything works pretty well except those pages.

[n-rodriguez]

Ok. From : https://github.com/Dolibarr/dolibarr/issues/2853#issuecomment-244480543

I've made a backup of the database then play htdocs/install/pgsql/functions/functions.sql. Everything works :)