ERROR: requested character too large for encoding: 14844072-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

In Oracle, this function expects an UTF8 encoded value. Now 14844072 in hex is E280A8, which corresponds to the UNICODE code point hex 2028, the "line separator" character.

In PostgreSQL, chr() expexts the code point as argument. So feed it the decimal value that corresponds to hex 2028:

SELECT chr(8232);