PostgreSQL doesn't support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

If you need to store the NULL character, you must use a bytea field - which should store anything you want, but won't support text operations on it.

Given that PostgreSQL doesn't support it in text values, there's no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it's likely going to be easier to do that in preprocessing before you load it.

Source:http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0

if (!IS_HIGHBIT_SET(*mbstr))
    {
      if (*mbstr != '\0')
      {
        mb_len++;
        mbstr++;
        len--;
        continue;
      }
      if (noError)
        return -1;
      report_invalid_encoding(encoding, mbstr, len);
    }

#define IS_HIGHBIT_SET(ch)   ((unsigned char)(ch)  HIGHBIT)
#define HIGHBIT         (0x80)

UPDATE: This seems to work:
 
Select * from TABLE
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
So:
 
Update TABLE
SET naughtyField = SUBSTRING(naughtyField, 1, LEN(naughtyField) - 1)
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
Source:http://stackoverflow.com/questions/3533320/sql-server-remove-end-string-character-0-from-data