Zero Character Code in String - Oracle to PostgreSQL Migration

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.

Oracle does not have such limitation:

Oracle:

  -- Sample table
  CREATE TABLE items
  (
    c1 CHAR(10)
  );
 
  INSERT INTO items VALUES ('a' || CHR(0) || 'b');
  # 1 row created.

But this INSERT statement fails in PostgreSQL:

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 an Oracle 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 Oracle to PostgreSQL Migration.