#82837Exception in backend searching in columns of type integer/bigint on PostgreSQL
Description
Define a column in a TYPO3 PostgreSQL database as integer or bigint, add it to TCA and make it available to the backend search by adding it to the TCA in ['ctrl']['searchFields']
Searching for a value in the backend record list leads to two different exceptions depending on the search configuration in TCA.
If you define the column as usual with simply
'config' => [
'type' => 'input',
]
an exception is thrown because the query tries to call LOWER() on the column value which fails for integer/bigint on PostgreSQL:
An exception occurred while executing
'SELECT COUNT(*) FROM "tx_my_table" WHERE ("tx_my_table"."pid" = 1) AND ((LOWER("my_column") LIKE LOWER('%MySearchTerm%'))'
SQLSTATE[42883]: Undefined function: 7
FEHLER: Funktion lower(bigint) existiert nicht
HINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
Configuring the search to be case-sensitive like this
'config' => [
'type' => 'input',
'search' => [
'case'
]
]
does not help as even if the LOWER() call is omitted the search fails too as doing a LIKE query on a integer/bigint column does not work either:
An exception occurred while executing
'SELECT COUNT(*) FROM "tx_my_table" WHERE ("tx_my_table"."pid" = 1) AND (("my_column" LIKE '%MySearchTerm%')'
SQLSTATE[42883]: Undefined function: 7
FEHLER: Operator existiert nicht: bigint ~~ unknown
HINT: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.