Re: Import/Export failure due to UTF-8 error in pgAdmin4 but not in pgAdmin3 - Mailing list pgadmin-support

On Mon, Jan 7, 2019 at 9:11 PM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Nania,
>
> Welcome to the wonderful world of pgAdmin4.  I have been bitten often by this particular shortcoming in pgAdmin4. :(
Myissue seems to stems from the fact that I use ASCII tables as a back end for a .Net windows application and perfectly
validwindows (Word/Excel) characters cause pgAdmin4 no end of issues. 

pgAdmin has nothing to do with this. It is simply calling PostgreSQL's
psql utility, and telling it to import or export the file. The
database server is then throwing the error seen.

> My solution (with the help of some fine people on the postgres IRC channel) is to run a couple of functions on my
tables/fieldsto locate and clean the offending characters out.  Of course, if you need those characters, then this
won'tactually help.  Here they are in the advent that they might prove helpful/adaptable to your situation. 

The problem with that is that you're trying to fix something that's
basically broken to begin with. From the PostgreSQL docs
(https://www.postgresql.org/docs/current/multibyte.html):

----
The SQL_ASCII setting behaves considerably differently from the other
settings. When the server character set is SQL_ASCII, the server
interprets byte values 0-127 according to the ASCII standard, while
byte values 128-255 are taken as uninterpreted characters. No encoding
conversion will be done when the setting is SQL_ASCII. Thus, this
setting is not so much a declaration that a specific encoding is in
use, as a declaration of ignorance about the encoding. In most cases,
if you are working with any non-ASCII data, it is unwise to use the
SQL_ASCII setting because PostgreSQL will be unable to help you by
converting or validating non-ASCII characters.
----


> Finds what pgAdmin4 considers bad UTF8:
> CREATE OR REPLACE FUNCTION live.is_utf8(
> text)
>     RETURNS boolean
>     LANGUAGE 'sql'
>
>     COST 100
>     VOLATILE
> AS $BODY$
>     select encode(convert_to($1,'SQL_ASCII'),'hex')
>            ~ $r$(?x)
>                 ^(?:(?:[0-7][0-9a-f])
>                    |(?:(?:c[2-9a-f]|d[0-9a-f])
>                       |e0[ab][0-9a-f]
>                       |ed[89][0-9a-f]
>                       |(?:(?:e[1-9abcef])
>                          |f0[9ab][0-9a-f]
>                          |f[1-3][89ab][0-9a-f]
>                          |f48[0-9a-f]
>                        )[89ab][0-9a-f]
>                     )[89ab][0-9a-f]
>                  )*$
>              $r$;
> $BODY$;
>
> ALTER FUNCTION live.is_utf8(text)
>     OWNER TO postgres;
>
>
> Fixes what pgAdmin4 considers to be bad UTF8:
>>
>> CREATE OR REPLACE FUNCTION live.badutf8(
>> text)
>>     RETURNS text
>>     LANGUAGE 'sql'
>>     COST 100
>>     VOLATILE
>> AS $BODY$
>>     select regexp_replace(encode(convert_to($1,'SQL_ASCII'),'hex'),
>>              $r$(?x)
>>                  (?:(?:[0-7][0-9a-f])
>>                    |(?:(?:c[2-9a-f]|d[0-9a-f])
>>                       |e0[ab][0-9a-f]
>>                       |ed[89][0-9a-f]
>>                       |(?:(?:e[1-9abcef])
>>                          |f0[9ab][0-9a-f]
>>                          |f[1-3][89ab][0-9a-f]
>>                          |f48[0-9a-f]
>>                        )[89ab][0-9a-f]
>>                     )[89ab][0-9a-f]
>>                  )*(..)?
>>              $r$, '-\1-', 'g')
>> $BODY$;
>> ALTER FUNCTION live.badutf8(text)
>>     OWNER TO postgres;
>
>
> Fixes bad UTF8
>
>
>
> On Mon, Jan 7, 2019 at 8:40 AM Nanina Tron <nanina.tron@icloud.com> wrote:
>>
>> Hi,
>>
>> I am pretty new to PostgreSQL so I might just miss something basic here.
>>
>> My problem is that, I cannot import or export some of the tables in my db with pgAdmin4, as it raises the “ERROR:
unvalidbyte-sequenz for coding »UTF8«: 0xdf 0x67“”. The table was originally created with Excel and imported via
pgAdmin3.The strange thing is that it can still be imported and exported with pgAdmin3 but not with pgAdmin4. The db
wascreated with encoding UTF-8, the .csv files where created with encoding UTF-8 and also the import/export dialog is
setto UTF-8. Queries are also no problem on these tables so it seems to me that this could be a client problem. 
>>
>> I am running PostgreSQL 11.1 on a server (I don’t know the OS, maintained with pgAdmin4). Locally I am working on a
Windows7 Professional (Service Pack 1) 64 Bit-System and pgAdmin4  3.6 & pgAdmin3. 
>>
>> I did not find any hint of the same problem on my Google or archive search, so I would be very grateful for any idea
whatI am doing wrong here. 
>>
>> Best,
>>
>> Nanina



--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company