NCHAR Function - SQL Server to PostgreSQL Migration

In SQL Server, the NCHAR function converts a Unicode code point to to the corresponding Unicode character value. In PostgreSQL you can use the CHR function but note that CHR(0) is not allowed.

SQL Server:

  -- Get Euro sign
  SELECT NCHAR(0x20AC);
  /* Result: € */
 
  -- Return <NUL> character (Unicode code point U+0000)
  SELECT NCHAR(0);
  /* Result: <NUL> character */

PostgreSQL:

  -- Get Euro sign
  SELECT CHR(0x20AC);
  /* Result: € */
 
    -- <NUL> character (Unicode code point U+0000) is not allowed in character types
  SELECT CHR(0);
  /* ERROR:  null character not permitted */

Note that you can successfully create a view, function or stored procedure containing CHR(0), you will get the error at runtime:

PostgreSQL:

  -- View with CHR(0)
  CREATE VIEW v1 AS SELECT CHR(0);
  /* Ok */
 
  -- Function with CHR(0)
  CREATE OR REPLACE FUNCTION f1() 
    RETURNS TEXT LANGUAGE SQL
       RETURN 'A' || CHR(0) || 'B'; 
  /* Ok */

Trying to query the view and call the function:

  SELECT * FROM v1;
  /* ERROR:  null character not permitted */
 
  SELECT f1();
  /* ERROR:  null character not permitted */

For more information, see SQL Server to PostgreSQL Migration.