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».
Если вам нужно хранить данные UTF8 в своей базе данных, вам нужна база данных, которая принимает UTF8. Вы можете проверить кодировку своей базы данных в pgAdmin. Просто щелкните правой кнопкой мыши базу данных и выберите «Свойства».
Но эта ошибка, похоже, говорит вам о некоторых недопустимых данных UTF8 в исходном файле. Это означает, что утилита copy обнаружила или предположила, что вы загружаете файл UTF8.
Если вы работаете под некоторым вариантом Unix, вы можете проверить кодировку (более или менее) с помощью file.
$ file yourfilename
yourfilename: UTF-8 Unicode English text
(Я думаю, что это будет работать и на Mac в терминале.) Не уверен, как это сделать в Windows.
Если вы используете ту же самую утилиту для файла, который поступает из систем Windows (то есть файла, который не закодирован в UTF8), он, вероятно, будет показывать что-то вроде этого:
$ file yourfilename
yourfilename: ASCII text, with CRLF line terminators
Если ситуация остается странной, вы можете попытаться преобразовать свои входные данные в известную кодировку, изменить свою клиентскую кодировку или и то, и другое. (Мы действительно растягиваем пределы моих знаний о кодировках.)
Вы можете использовать утилиту iconv для изменения кодировки входных данных.
iconv -f original_charset -t utf-8 originalfile > newfile
Вы можете изменить кодировку psql (клиент), следуя инструкциям Поддержка набора символов. На этой странице найдите фразу «Включить автоматическое преобразование набора символов».
I have a simple SQL syntax for inserting to table. I’m using Postgresql 8.4 and already set Database encoding to be UTF8, and POSIX for Collation and Character type.
The query is fine if i run it under pgadmin3, but bring error if i execute in PHP.
"Internal Server Error: SQLSTATE[22021]:
Character not in repertoire: 7 ERROR:
invalid byte sequence for encoding "UTF8": 0xd85bnHINT:
This error can also happen if the byte sequence does not match the encoding expected by the server,
which is controlled by "client_encoding"
So i tried to set NAMES and client_encoding from PHP(PDO), but still have the same problem
$instance->exec("SET client_encoding = 'UTF8';");
$instance->exec("SET NAMES 'UTF8';");
pg_set_client_encoding($link, "UNICODE"); my be work if i’m using native postgresql driver pg_pconnect, but currently i’m using PDO as Driver.
and i also already set mb_internal_encoding('UTF-8');
Is there any other way to fix this issue ?
This error only appear when i trying to insert non ascii word like arabic or japanese word
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».
Если вам нужно хранить данные UTF8 в своей базе данных, вам нужна база данных, которая принимает UTF8. Вы можете проверить кодировку своей базы данных в pgAdmin. Просто щелкните правой кнопкой мыши базу данных и выберите «Свойства».
Но эта ошибка, похоже, говорит вам о некоторых недопустимых данных UTF8 в исходном файле. Это означает, что утилита copy обнаружила или предположила, что вы загружаете файл UTF8.
Если вы работаете под некоторым вариантом Unix, вы можете проверить кодировку (более или менее) с помощью file.
$ file yourfilename
yourfilename: UTF-8 Unicode English text
(Я думаю, что это будет работать и на Mac в терминале.) Не уверен, как это сделать в Windows.
Если вы используете ту же самую утилиту для файла, который поступает из систем Windows (то есть файла, который не закодирован в UTF8), он, вероятно, будет показывать что-то вроде этого:
$ file yourfilename
yourfilename: ASCII text, with CRLF line terminators
Если ситуация остается странной, вы можете попытаться преобразовать свои входные данные в известную кодировку, изменить свою клиентскую кодировку или и то, и другое. (Мы действительно растягиваем пределы моих знаний о кодировках.)
Вы можете использовать утилиту iconv для изменения кодировки входных данных.
iconv -f original_charset -t utf-8 originalfile > newfile
Вы можете изменить кодировку psql (клиент), следуя инструкциям Поддержка набора символов. На этой странице найдите фразу «Включить автоматическое преобразование набора символов».
In this article, we will see how you can fix error ‘invalid byte sequence for encoding UTF8’ while restoring a PostgreSQL database. At work, I got a task to move DBs which has ASCII encoding to UTF8 encoding. Let me first confess that the ASCII DBs was not created by intention, someone accidentally created it!!! Having a DB ASCII encoded is very dangerous, it should be moved to UTF8 encoding as soon as possible. So the initial plan was to create archive dump of the DB with pg_dump , create a new DB with UTF8 encoding and restore the dump to the new DB using pg_restore . The plan worked for most of the DBs, but failed for one DB with below error.
DETAIL: Proceeding with relation creation anyway. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 35091; 0 2527787452 TABLE DATA my_table release pg_restore: [archiver (db)] COPY failed for table "my_table": ERROR: invalid byte sequence for encoding "UTF8": 0xa5 CONTEXT: COPY my_table, line 41653 WARNING: errors ignored on res
As the error says, there are some invalid UTF8 characters in table “my_table” which prevents pg_restore from restoring the particular table. I did a lot of research and googling to see what to do. I will list out what all steps I did.
Assume ‘my_db’ and ‘my_table’ is the database name and table name respectively.
Step 1:
Dump the Database excluding particular table ‘my_table’. I would suggest dumping the database in archive format for saving time and disk space.
pg_dump -Fc -T 'my_table' -p 1111 -f dbdump.pgd my_db
Step 2:
Create the new database with UTF8 encoding and restore the dump.
pg_restore -p 2222 -j 8 -d my_new_db dbdump.pgd
The restoration should be successful as we didn’t restore the offending table.
Step 3:
Dump the offending table ‘my_table’ in plain text format.
pg_dump -Fp -t 'my_table' -p 1111 my_db > my_db_table_only.sql
Step 4:
Now we have table data in plain text. Let’s find invalid UTF8 characters in the file by running below command(make sure locale is set to UTF-8,).
# grep -naxv '.*' my_db_table_only.sql 102:2010-03-23 ��ԥ� data1 data2
� represents an invalid UTF8 character and it is present in 102th line of the file.
Step 5:
Find which charset the invalid UTF8 characters belongs to.
#grep -naxv '.*' my_db_table_only.sql > test.txt #file -i test.txt test.txt: text/plain; charset=iso-8859-1
As per the output, those characters belongs to iso-8859-1. The charset may be different in your case.
Step 6:
Let’s convert iso-8859-1 to UTF8 using iconv command.
#grep -naxv '.*' my_db_table_only.sql | iconv --from-code=ISO-8859-1 --to-code=UTF-8 102:2010-03-23 ¥Êԥ¡ data1 data2
Now you got the characters in UTF8 encoding. So you can just replace ��ԥ� with ¥Êԥ¡ in 102th line of dump file(I used nano editor to do this, faced issues with Vim .)
I know that replacing characters manually could be a pain in the ass if there are lot of invalid UTF8 characters. We can run iconv on the whole file as shown below.
iconv --from-code=ISO-8859-1 --to-code=UTF-8 my_db_table_only.sql > my_db_table_only_utf8.sql
But I won’t recommend this as it may change valid characters(eg: Chinese characters ) to some other characters. If you plan to run iconv on the file, just make sure only invalid UTF8 characters are converted by taking diff of both files.
Step7.
Once the characters are replaced. Restore the table to the database.
psql -p 2222 -d my_new_db -f my_db_table_only.sql
No more “Invalid byte sequence for encoding UTF8” error. Thanks for the time taken to read my blog. Subscribe to this blog so that you don’t miss out anything useful (Checkout Right Sidebar for the Subscription Form and Facebook follow button) . Please also put your thoughts as comments .
Если вам нужно хранить данные UTF8 в своей базе данных, вам нужна база данных, которая принимает UTF8. Вы можете проверить кодировку вашей базы данных в pgAdmin. Просто щелкните правой кнопкой мыши базу данных и выберите «Свойства».
Но эта ошибка, кажется, говорит вам, что в исходном файле есть недопустимые данные UTF8. Это означает, что copy утилита обнаружила или предположила, что вы передаете ей файл UTF8.
Если вы работаете под какой-либо версией Unix, вы можете проверить кодировку (более или менее) с помощью file утилита.
$ file yourfilename
yourfilename: UTF-8 Unicode English text
(Я думаю, что это будет работать и на Mac в терминале.) Не знаю, как это сделать под Windows.
Если вы используете эту же утилиту для файла, полученного из систем Windows (т. е. файла, не в кодировке UTF8), вероятно, будет отображаться что-то вроде этого:
$ file yourfilename
yourfilename: ASCII text, with CRLF line terminators
Если что-то останется странным, вы можете попытаться преобразовать свои входные данные в известную кодировку, изменить кодировку вашего клиента или и то, и другое. (Мы действительно расширяем границы моих знаний о кодировках.)
Вы можете использовать iconv утилита для изменения кодировки входных данных.
iconv -f original_charset -t utf-8 originalfile > newfile
Вы можете изменить кодировку psql (клиент), следуя инструкциям на Поддержка набора символов. На этой странице найдите фразу «Чтобы включить автоматическое преобразование набора символов».
I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I’m using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the custom/compress format (.backup) and UTF8 encoding.
The original database is in UTF8, like so:
-- Database: favela
-- DROP DATABASE favela;
CREATE DATABASE favela
WITH OWNER = favela
ENCODING = 'UTF8'
TABLESPACE = favela
CONNECTION LIMIT = -1;
I’m creating this database exactly like this on the destination server. But when I restore the database from the .backup file using the Restore option it gives me some of these errors:
pg_restore: restoring data for table "arena"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2173; 0 35500 TABLE DATA arena favela
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xe3a709
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT: COPY arena, line 62
When I check which record triggered this error in fact some vartext fields have diacritical characters like ç (used in Portuguese, for example, «caça»), and when I manually remove them from the text in the records the error passes to the next record that has them — since when copy has an error it stops inserting data on this table. And I don’t want to replace them manually one by one to accomplish this.
But it’s kinda of strange because with UTF8 there shouldn’t be this kind of problems, right?
I don’t know how they got there in the first place. I’m only migrating the database, and I supose that somehow the database was like in LATIN1 and then was improperly changed to UTF8.
Is there any way to check if a table/database has invalid UTF8 sequences? Or any way to enforce/reconvert these characters into UFT8 so I don’t run into any problems when I execute the restore?
Thanks, in advance.
I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I’m using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the custom/compress format (.backup) and UTF8 encoding.
The original database is in UTF8, like so:
-- Database: favela
-- DROP DATABASE favela;
CREATE DATABASE favela
WITH OWNER = favela
ENCODING = 'UTF8'
TABLESPACE = favela
CONNECTION LIMIT = -1;
I’m creating this database exactly like this on the destination server. But when I restore the database from the .backup file using the Restore option it gives me some of these errors:
pg_restore: restoring data for table "arena"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2173; 0 35500 TABLE DATA arena favela
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xe3a709
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT: COPY arena, line 62
When I check which record triggered this error in fact some vartext fields have diacritical characters like ç (used in Portuguese, for example, «caça»), and when I manually remove them from the text in the records the error passes to the next record that has them — since when copy has an error it stops inserting data on this table. And I don’t want to replace them manually one by one to accomplish this.
But it’s kinda of strange because with UTF8 there shouldn’t be this kind of problems, right?
I don’t know how they got there in the first place. I’m only migrating the database, and I supose that somehow the database was like in LATIN1 and then was improperly changed to UTF8.
Is there any way to check if a table/database has invalid UTF8 sequences? Or any way to enforce/reconvert these characters into UFT8 so I don’t run into any problems when I execute the restore?
Thanks, in advance.
I’ve spent the last 8 hours trying to import the output of ‘mysqldump —compatible=postgresql’ into PostgreSQL 8.4.9, and I’ve read at least 20 different threads here and elesewhere already about this specific problem, but found no real usable answer that works.
MySQL 5.1.52 data dumped:
mysqldump -u root -p --compatible=postgresql --no-create-info --no-create-db --default-character-set=utf8 --skip-lock-tables rt3 > foo
PostgreSQL 8.4.9 server as destination
Loading the data with ‘psql -U rt_user -f foo’ is reporting (many of these, here’s one example):
psql:foo:29: ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
According the following, there are no NULL (0x00) characters in the input file.
database-dumps:rcf-temp1# sed 's/x0/ /g' < foo > nonulls
database-dumps:rcf-temp1# sum foo nonulls
04730 2545610 foo
04730 2545610 nonulls
database-dumps:rcf-temp1# rm nonulls
Likewise, another check with Perl shows no NULLs:
database-dumps:rcf-temp1# perl -ne '/00/ and print;' foo
database-dumps:rcf-temp1#
As the «HINT» in the error mentions, I have tried every possible way to set ‘client_encoding’ to ‘UTF8’, and I succeed but it has no effect toward solving my problem.
database-dumps:rcf-temp1# psql -U rt_user --variable=client_encoding=utf-8 -c "SHOW client_encoding;" rt3
client_encoding
-----------------
UTF8
(1 row)
database-dumps:rcf-temp1#
Perfect, yet:
database-dumps:rcf-temp1# psql -U rt_user -f foo --variable=client_encoding=utf-8 rt3
...
psql:foo:29: ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
...
Barring the «According to Hoyle» correct answer, which would be fantastic to hear, and knowing that I really don’t care about preserving any non-ASCII characters for this seldom-referenced data, what suggestions do you have?
Update: I get the same error with an ASCII-only version of the same dump file at import time. Truly mind-boggling:
database-dumps:rcf-temp1# # convert any non-ASCII character to a space
database-dumps:rcf-temp1# perl -i.bk -pe 's/[^[:ascii:]]/ /g;' mysql5-dump.sql
database-dumps:rcf-temp1# sum mysql5-dump.sql mysql5-dump.sql.bk
41053 2545611 mysql5-dump.sql
50145 2545611 mysql5-dump.sql.bk
database-dumps:rcf-temp1# cmp mysql5-dump.sql mysql5-dump.sql.bk
mysql5-dump.sql mysql5-dump.sql.bk differ: byte 1304850, line 30
database-dumps:rcf-temp1# # GOOD!
database-dumps:rcf-temp1# psql -U postgres -f mysql5-dump.sql --variable=client_encoding=utf-8 rt3
...
INSERT 0 416
psql:mysql5-dump.sql:30: ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encod.
INSERT 0 455
INSERT 0 424
INSERT 0 483
INSERT 0 447
INSERT 0 503
psql:mysql5-dump.sql:36: ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encod.
INSERT 0 502
INSERT 0 507
INSERT 0 318
INSERT 0 284
psql:mysql5-dump.sql:41: ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encod.
INSERT 0 382
INSERT 0 419
INSERT 0 247
psql:mysql5-dump.sql:45: ERROR: invalid byte sequence for encoding "UTF8": 0x00
HINT: This error can also happen if the byte sequence does not match the encod.
INSERT 0 267
INSERT 0 348
^C
One of the tables in question is defined as:
Table "public.attachments"
Column | Type | Modifie
-----------------+-----------------------------+--------------------------------
id | integer | not null default nextval('atta)
transactionid | integer | not null
parent | integer | not null default 0
messageid | character varying(160) |
subject | character varying(255) |
filename | character varying(255) |
contenttype | character varying(80) |
contentencoding | character varying(80) |
content | text |
headers | text |
creator | integer | not null default 0
created | timestamp without time zone |
Indexes:
"attachments_pkey" PRIMARY KEY, btree (id)
"attachments1" btree (parent)
"attachments2" btree (transactionid)
"attachments3" btree (parent, transactionid)
I do not have the liberty to change the type for any part of the DB schema. Doing so would likely break future upgrades of the software, etc.
The likely problem column is ‘content’ of type ‘text’ (perhaps others in other tables as well). As I already know from previous research, PostgreSQL will not allow NULL in ‘text’ values. However, please see above where both sed and Perl show no NULL characters, and then further down where I strip all non-ASCII characters from the entire dump file but it still barfs.
Мне было поручено перенести базу данных PostgreSQL 8.2.x на другой сервер. Для этого я использую pgAdmin 1.12.2 (кстати, в Ubuntu 11.04), а также использую резервное копирование и восстановление, используя формат custom / compress (.backup) и кодировку UTF8.
Исходная база данных находится в UTF8, вот так:
-- Database: favela
-- DROP DATABASE favela;
CREATE DATABASE favela
WITH OWNER = favela
ENCODING = 'UTF8'
TABLESPACE = favela
CONNECTION LIMIT = -1;
Я создаю эту базу данных точно так же, как это на целевом сервере. Но когда я восстанавливаю базу данных из файла .backup, используя опцию Восстановить, это дает мне некоторые из этих ошибок:
pg_restore: restoring data for table "arena"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2173; 0 35500 TABLE DATA arena favela
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xe3a709
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT: COPY arena, line 62
Когда я проверяю, какая запись вызвала эту ошибку, на самом деле в некоторых полях из текстового поля есть диакритические символы, такие как ç (используется на португальском языке, например, «caça»), и когда я вручную удаляю их из текста в записях, ошибка переходит к следующей записи. у них есть — поскольку при копировании возникает ошибка, он прекращает вставку данных в эту таблицу. И я не хочу заменять их вручную один за другим для достижения этой цели.
Но это немного странно, потому что с UTF8 не должно быть таких проблем, верно?
Я не знаю, как они туда попали. Я только перемещаю базу данных, и я полагаю, что база данных каким-то образом была похожа на LATIN1, а затем была неправильно изменена на UTF8.
Есть ли способ проверить, есть ли в таблице / базе данных недопустимые последовательности UTF8? Или какой-нибудь способ принудительно / преобразовать эти символы в UFT8, чтобы у меня не возникало проблем при выполнении восстановления?
Заранее спасибо.
In this article, we will see how you can fix error ‘invalid byte sequence for encoding UTF8’ while restoring a PostgreSQL database. At work, I got a task to move DBs which has ASCII encoding to UTF8 encoding. Let me first confess that the ASCII DBs was not created by intention, someone accidentally created it!!! Having a DB ASCII encoded is very dangerous, it should be moved to UTF8 encoding as soon as possible. So the initial plan was to create archive dump of the DB with pg_dump , create a new DB with UTF8 encoding and restore the dump to the new DB using pg_restore . The plan worked for most of the DBs, but failed for one DB with below error.
DETAIL: Proceeding with relation creation anyway. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 35091; 0 2527787452 TABLE DATA my_table release pg_restore: [archiver (db)] COPY failed for table "my_table": ERROR: invalid byte sequence for encoding "UTF8": 0xa5 CONTEXT: COPY my_table, line 41653 WARNING: errors ignored on res
As the error says, there are some invalid UTF8 characters in table “my_table” which prevents pg_restore from restoring the particular table. I did a lot of research and googling to see what to do. I will list out what all steps I did.
Assume ‘my_db’ and ‘my_table’ is the database name and table name respectively.
Step 1:
Dump the Database excluding particular table ‘my_table’. I would suggest dumping the database in archive format for saving time and disk space.
pg_dump -Fc -T 'my_table' -p 1111 -f dbdump.pgd my_db
Step 2:
Create the new database with UTF8 encoding and restore the dump.
pg_restore -p 2222 -j 8 -d my_new_db dbdump.pgd
The restoration should be successful as we didn’t restore the offending table.
Step 3:
Dump the offending table ‘my_table’ in plain text format.
pg_dump -Fp -t 'my_table' -p 1111 my_db > my_db_table_only.sql
Step 4:
Now we have table data in plain text. Let’s find invalid UTF8 characters in the file by running below command(make sure locale is set to UTF-8,).
# grep -naxv '.*' my_db_table_only.sql 102:2010-03-23 ��ԥ� data1 data2
� represents an invalid UTF8 character and it is present in 102th line of the file.
Step 5:
Find which charset the invalid UTF8 characters belongs to.
#grep -naxv '.*' my_db_table_only.sql > test.txt #file -i test.txt test.txt: text/plain; charset=iso-8859-1
As per the output, those characters belongs to iso-8859-1. The charset may be different in your case.
Step 6:
Let’s convert iso-8859-1 to UTF8 using iconv command.
#grep -naxv '.*' my_db_table_only.sql | iconv --from-code=ISO-8859-1 --to-code=UTF-8 102:2010-03-23 ¥Êԥ¡ data1 data2
Now you got the characters in UTF8 encoding. So you can just replace ��ԥ� with ¥Êԥ¡ in 102th line of dump file(I used nano editor to do this, faced issues with Vim .)
I know that replacing characters manually could be a pain in the ass if there are lot of invalid UTF8 characters. We can run iconv on the whole file as shown below.
iconv --from-code=ISO-8859-1 --to-code=UTF-8 my_db_table_only.sql > my_db_table_only_utf8.sql
But I won’t recommend this as it may change valid characters(eg: Chinese characters ) to some other characters. If you plan to run iconv on the file, just make sure only invalid UTF8 characters are converted by taking diff of both files.
Step7.
Once the characters are replaced. Restore the table to the database.
psql -p 2222 -d my_new_db -f my_db_table_only.sql
No more “Invalid byte sequence for encoding UTF8” error. Thanks for the time taken to read my blog. Subscribe to this blog so that you don’t miss out anything useful (Checkout Right Sidebar for the Subscription Form and Facebook follow button) . Please also put your thoughts as comments .