Fix: “operator does not exist: integer || integer” in PostgreSQL

If you get the “operator does not exist: integer || integer” error in PostgreSQL, it’s probably because you’re trying to concatenate two numbers.

If you really want to concatenate two numbers, the easiest way to overcome this issue is to cast at least one of them to a string data type first.

Another way to do it is to use the CONCAT() function.

Example of Error

Here’s an example of code that causes this error:

SELECT 123 || 456;

Result:

ERROR:  operator does not exist: integer || integer
LINE 1: SELECT 123 || 456;
                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Solution 1

Here’s a quick way to overcome this issue:

SELECT CAST(123 AS varchar(3)) || 456;

Result:

While it’s true that we could have cast both numbers to a string, it’s not necessary. As long as one of the operands is a string, the pipe concatenation operator will be able to concatenate them.

Solution 2

Another way to do it is to use the CONCAT() function to do the concatenation:

SELECT CONCAT(123, 456);

Result:

This function works on numbers without the need to convert any of them to a string. The function automatically does this (it concatenates the text representations of its arguments by default).