ERROR: requested character too large for encoding: 14844072

Question

I am converting following line of code from Oracle to PostgreSQL.

In Oracle:

select CHR(14844072) from dual

Output:

"
"

In postgresql:

select CHR(14844072);

Getting an error:

SQL Error [54000]: ERROR: requested character too large for encoding: 14844072

Answer 1

The behavior of the function is different from Oracle to Postgresql.
In oracle the statement is valid. So is, for example:

select CHR(0) from dual;

While in Postgresql, you can't SELECT CHR(0):

chr(0) is disallowed because text data types cannot store that character.

Source: https://www.postgresql.org/docs/14/functions-string.html

This is just an example. More specific: what do you expect with value 14844072? Empty string is nonsense for Postgresql.

In Oracle you have this situation:

• For single-byte character sets, if n > 256, then Oracle Database returns the binary equivalent of n mod 256
• For multibyte character sets, n must resolve to one entire code point

But:

Invalid code points are not validated, and the result of specifying invalid code points is indeterminate.

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions019.htm

In PostgreSQL the function depends from encoding, but, assuming you use UTF8:

In UTF8 encoding the argument is treated as a Unicode code point. In other multibyte encodings the argument must designate an ASCII character

Short answer: you need to work on the application code OR build your own function, something like this (just en example):

CREATE OR REPLACE FUNCTION myCHR(integer) RETURNS TEXT
AS $$
BEGIN
    IF $1 = 0 THEN
        RETURN '';
    ELSE 
        IF $1 <= 1114111 THEN --replace the number according to your encoding
            RETURN CHR($1);
        ELSE
            RETURN '';
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;