Zero Character Code in String - Informix 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.

Informix does not have such limitation:

Informix:

  -- Sample table
  CREATE TABLE items
  (
    c1 CHAR(10)
  );
 
  INSERT INTO items VALUES ('a' || CHR(0) || 'b');
  # 1 row created.
 
  -- Review inserted data
  SELECT
     ASCII(SUBSTRING(c1 FROM 1 FOR 1)), 
     ASCII(SUBSTRING(c1 FROM 2 FOR 1)), 
     ASCII(SUBSTRING(c1 FROM 3 FOR 1)) 
  FROM items;
  #  97            0           98

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