CHAR Function - SQL Server to PostgreSQL Migration

In SQL Server, the CHAR function converts an integer ASCII code (0-255) to the corresponding character value. In PostgreSQL you can use the CHR function but note that CHR(0) is not allowed.

SQL Server:

  -- CHAR(32) returns a blank character
  SELECT CHAR(32);
  /* Result: ' ' */
 
  -- NULL is returned for values exceeding 255
  SELECT CHAR(270);
  /* NULL */
 
  -- Return <NUL> character (ASCII code 0x00)
  SELECT CHAR(0);
  /* Result: <NUL> character */

PostgreSQL:

  -- CHR(32) returns a blank character
  SELECT CHR(32);
  /* Result: ' ' */
 
    -- <NUL> character (ASCII code 0x00) 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.