invalid byte sequence for encoding “UTF8”

by

If you need to store UTF8 data in your database, you need a database that accepts UTF8. You can check the encoding of your database in pgAdmin. Just right-click the database, and select “Properties”.

But that error seems to be telling you there’s some invalid UTF8 data in your source file. That means that the copy utility has detected or guessed that you’re feeding it a UTF8 file.

If you’re running under some variant of Unix, you can check the encoding (more or less) with the file utility.

$ file yourfilename
yourfilename: UTF-8 Unicode English text

(I think that will work on Macs in the terminal, too.) Not sure how to do that under Windows.

If you use that same utility on a file that came from Windows systems (that is, a file that’s not encoded in UTF8), it will probably show something like this:

$ file yourfilename
yourfilename: ASCII text, with CRLF line terminators

If things stay weird, you might try to convert your input data to a known encoding, to change your client’s encoding, or both. (We’re really stretching the limits of my knowledge about encodings.)

You can use the iconv utility to change encoding of the input data.

iconv -f original_charset -t utf-8 originalfile > newfile

You can change psql (the client) encoding following the instructions on Character Set Support. On that page, search for the phrase “To enable automatic character set conversion”.

More Related Contents:

  1. How to reset postgres’ primary key sequence when it falls out of sync?
  2. PostgreSQL LIKE query performance variations
  3. How to import CSV file data into a PostgreSQL table
  4. The forgotten assignment operator “=” and the commonplace “:=”
  5. Equivalent to unpivot() in PostgreSQL
  6. Passing user id to PostgreSQL triggers
  7. Why does PostgreSQL perform sequential scan on indexed column?
  8. PostgreSQL naming conventions
  9. In-order sequence generation
  10. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  11. How to generate the “create table” sql statement for an existing table in postgreSQL
  12. PostgreSQL: serial vs identity
  13. Postgres password authentication fails
  14. Generate series of dates – using date type as input
  15. Postgres query to check a string is a number
  16. Using pyspark to connect to PostgreSQL
  17. Returning from a function with OUT parameter
  18. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  19. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  20. Joining Results from Two Separate Databases
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  22. Update timestamp when row is updated in PostgreSQL
  23. Postgresql query array of objects in JSONB field
  24. Multicolumn index on 3 fields with heterogenous data types
  25. How to add a conditional unique index on PostgreSQL
  26. Select from a table variable
  27. How to delete an enum type value in postgres?
  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