Error in convert_from() | SQL_ASCII

Question

When trying to use the function convert_from of postgresql I am facing the following error:

invalid byte sequence for encoding "UTF8": 0xe3 0x30 0x30

I’ve looked here in the stack and found nothing that can help me.

Answer 1

You need to identify the encoding of all its inputs and outputs to avoid confusion and not lose the original data along the way.

First, you need to identify the encoding in which your file .txt was recorded.

In systems *nix you can use the utility file to detect the encoding file:

$ file entrada1.txt 
entrada1.txt: UTF-8 Unicode text

$ file entrada2.txt 
entrada2.txt: ISO-8859 text

$ file entrada3.txt 
entrada3.txt: ASCII text, with CRLF line terminators

Identifying the encoding of each file, you will be able to convert them properly using the function convert_from():

-- Lendo arquivo UTF-8/UNICODE
SELECT convert_from( pg_read_binary_file( 'entrada1.txt' ), 'UTF8' );

-- Lendo arquivo LATIN-1 / ISO-8859-1
SELECT convert_from( pg_read_binary_file( 'entrada2.txt' ), 'LATIN1' );

-- Lendo arquivo puramente ASCII
SELECT convert_from( pg_read_binary_file( 'entrada3.txt' ), 'SQL_ASCII' );