TYPO3 Core

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.