Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00

by

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

Source: http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE

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.

More Related Contents:

  1. What to do with null values when modeling and normalizing?
  2. Postgres: INSERT if does not exist already
  3. Truncating all tables in a Postgres database
  4. postgres default timezone
  5. How to change PostgreSQL user password?
  6. How to get the count of current month Sunday’s in psql?
  7. Restore a postgres backup file using the command line?
  8. How to close idle connections in PostgreSQL automatically?
  9. Refresh a materialized view automatically using a rule or notify
  10. postgresql – replace all instances of a string within text field
  11. Is it possible to have autoincrement number without it being an ID?
  12. PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
  13. pgadmin4 : postgresql application server could not be contacted.
  14. PostgreSQL error ‘Could not connect to server: No such file or directory’
  15. Insert if not exists, else return id in postgresql
  16. function returns multiple columns as a single column instead of multiple columns
  17. Sequences not affected by transactions?
  18. Postgres: check if array field contains value?
  19. Dynamic pivot query using PostgreSQL 9.3
  20. Omitting the double quote to do query on PostgreSQL
  21. Storing Images in PostgreSQL
  22. Psql could not connect to server: No such file or directory, 5432 error?
  23. In PostgreSQL, how to insert data with COPY command?
  24. How to add column if not exists on PostgreSQL?
  25. Maximum characters in labels (table names, columns etc)
  26. Docker – Postgres and pgAdmin 4 : Connection refused
  27. How to work with PGpoint for Geolocation using PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment