PostgreSQL Server - Zero Character Code in String

PostgreSQL does not allow you to insert the character with code zero (ASCII NUL) to CHAR, VARCHAR or TEXT columns regardless of the specific character set.

For the example, the following INSERT statement fails:

PostgreSQL:

  -- Sample table
  CREATE TABLE items
  (
    c1 CHAR(10)
  );
 
  INSERT INTO items VALUES ('a' || CHR(0) || 'b');
  # ERROR:  null character not permitted

This is documented at

• https://www.postgresql.org/docs/current/datatype-character.html
• https://www.postgresql.org/docs/current/functions-string.html, see CHR() function notes.

When you migrate a database that allows storing characters with zero codes (Oracle i.e.) to PostgreSQL SQLines Data tool offers -pg_zero_char_replace option that allows you to replace the characters with zero codes with the specified character i.e. blanks.

For more information, see PostgreSQL Migration Reference.