Обсуждение: Import/Export failure due to UTF-8 error in pgAdmin4 but not in pgAdmin3

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: unvalid byte-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 was created with encoding UTF-8, the .csv files where created with encoding UTF-8 and also the import/export dialog is set to 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 Windows 7 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 what I am doing wrong here.

Best,

Nanina

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. :(  My issue seems to stems from the fact that I use ASCII tables as a back end for a .Net windows application and perfectly valid windows (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/fields to locate and clean the offending characters out.  Of course, if you need those characters, then this won't actually 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: unvalid byte-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 was created with encoding UTF-8, the .csv files where created with encoding UTF-8 and also the import/export dialog is set to 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 Windows 7 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 what I 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

Hi

On Mon, Jan 7, 2019 at 11:30 PM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Dave,
>
> I can't speak to Nania's specific issue, but I believe it's a pgAdmin4 specific problem, at least in so far as SQL_ASCII is concerned.  I say this because I can usually work with the data just fine from the psql prompt, but not through pgAdmin4 (or other postgreSQL GUI's like dBeaver that rely on the JDBC connection).  .Net/Windows ODBC drivers and psql command prompt, no problem (as was pgAdmin3 assuming you don't do too much with it beyond select/update/insert).  pgAdmin4, SELECT, export, etc. BOOM! At least until you cleaned  up the offending bytes.
>
> Just my $0.02.

I'm afraid the fundamental problem is that you're using PostgreSQL in
a way that the docs specifically recommend against doing, and you're
seeing the reason why.

pgAdmin 3 and 4 are completely different. In the import/export utility
that Nania reported the issue in, pgAdmin doesn't look at the data *at
all*. It simply executes \copy in psql, which does all the work. All
pgAdmin does is provide connection info and options to psql, based on
the selections made in the import/export dialogue, and executes it.

In other areas of pgAdmin, like the query tool, it is possible to see
similar issues with the same underlying cause, though we've spent a
significant amount of time trying to work around all the possible edge
cases.

pgAdmin 3 implemented import/export itself, using underlying libraries
that were far less strict about encoding rules than Python is. That
may have been more convenient for this particular issue, but it's a
lot worse in many others.

As a general thought (and do bear in mind, we've spent significant
time and resources on these issues in the past), I'd far rather spend
time on new features and actual bugs, than further time on workarounds
for things the PostgreSQL docs specifically advise against doing.

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

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

Hi

On Tue, Jan 8, 2019 at 12:47 AM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Dave,
>
> Thanks for taking the time to respond, but I don't see anywhere that SQL_ASCII is recommended against doing. Here's the documentation listing the supported encoding schemas: https://www.postgresql.org/docs/current/multibyte.html .
>
> The only caveats listed for SQL_ASCII are:
>>
>> 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.

You highlighted it below: "If the client character set is defined as
SQL_ASCII, encoding conversion is disabled, regardless of the server's
character set. Just as for the server, use of SQL_ASCII is unwise
unless you are working with all-ASCII data"

You're using UTF-8 data, not ASCII, which it says is unwise because
conversion won't take place (and consequently, neither will
validation). I don't see how one could read that and not take it as

You are running into exactly that problem; and it's visible when
working with technologies that are strict about following encoding
rules - in this case, psql when pgAdmin shells out to it.

I did think of one possible quick fix this morning which I'll look
into, but as I noted before; it's a workaround, and the real problem
is storing un-validated UTF-8 data in a SQL_ASCII database.

> Or, a reminder that postgreSQL can't help with any conversions you might want to do.
>
> Then there's this:
>>
>> PostgreSQL will allow superusers to create databases with SQL_ASCII encoding even when LC_CTYPE is not C or POSIX. As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.
>
>
> A note that you can currently choose incompatible settings, but probably can't in the future.
>
> And finally there's this bit of advice:
>>
>> If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server's character set. Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data[emphasis mine].
>
>
> Which is just a reiteration of the first caveat, that if you are using SQL_ASCII the database won't perform any conversions on your behalf.
>
> That is hardly a recommendation against using that supported encoding scheme.  The fact that the psql command prompt, among others, works with it without issue, is an indication that the problem lies in pgAdmin4 (and I would guess the reliance of python on UTF8) than an issue with the database itself.  pgAdmin4 needs to check for and more gracefully handle valid postgreSQL data that might happen to be not UTF8 compliant.
>
> Until then, I will have to periodically scan and clean for bad UTF8 data to keep pgAdmin4 (and other JDBC dependent code) happy.  The legacy enterprise .Net applications that depend on it prohibit converting it to UTF8 (or anything else for that matter).
>
> Just my $0.02,
>
> rik.
>
>
> On Mon, Jan 7, 2019 at 1:27 PM Dave Page <dpage@pgadmin.org> wrote:
>>
>> Hi
>>
>> On Mon, Jan 7, 2019 at 11:30 PM richard coleman
>> <rcoleman.ascentgl@gmail.com> wrote:
>> >
>> > Dave,
>> >
>> > I can't speak to Nania's specific issue, but I believe it's a pgAdmin4 specific problem, at least in so far as SQL_ASCII is concerned.  I say this because I can usually work with the data just fine from the psql prompt, but not through pgAdmin4 (or other postgreSQL GUI's like dBeaver that rely on the JDBC connection).  .Net/Windows ODBC drivers and psql command prompt, no problem (as was pgAdmin3 assuming you don't do too much with it beyond select/update/insert).  pgAdmin4, SELECT, export, etc. BOOM! At least until you cleaned  up the offending bytes.
>> >
>> > Just my $0.02.
>>
>> I'm afraid the fundamental problem is that you're using PostgreSQL in
>> a way that the docs specifically recommend against doing, and you're
>> seeing the reason why.
>>
>> pgAdmin 3 and 4 are completely different. In the import/export utility
>> that Nania reported the issue in, pgAdmin doesn't look at the data *at
>> all*. It simply executes \copy in psql, which does all the work. All
>> pgAdmin does is provide connection info and options to psql, based on
>> the selections made in the import/export dialogue, and executes it.
>>
>> In other areas of pgAdmin, like the query tool, it is possible to see
>> similar issues with the same underlying cause, though we've spent a
>> significant amount of time trying to work around all the possible edge
>> cases.
>>
>> pgAdmin 3 implemented import/export itself, using underlying libraries
>> that were far less strict about encoding rules than Python is. That
>> may have been more convenient for this particular issue, but it's a
>> lot worse in many others.
>>
>> As a general thought (and do bear in mind, we've spent significant
>> time and resources on these issues in the past), I'd far rather spend
>> time on new features and actual bugs, than further time on workarounds
>> for things the PostgreSQL docs specifically advise against doing.
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company



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

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

Hi Rik

On Tue, Jan 8, 2019 at 6:53 PM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Dave,
>
> Thanks for continuing this discussion, but I think you misunderstand the situation.  I am storing valid non-UTF8 data in a SQL_ASCII encoded postgreSQL database (please re-read what I had previously written). This is why psql has NO problem dealing with it.  This is also why Windows ODBC and .Net applications have NO problem dealing with it.  In fact the most common character that pgAdmin4 crashes on is the Windows smart quote.  So to reiterate, I am using valid non-UTF8 characters in a SQL_ASCII database.  This is a supported configuration for postgreSQL.  The issue seems to be that pgAdmin4 is assuming  UTF8 data and crashing/failing/throwing errors when it encounters invalid UTF8 characters.
>
> I hope I have made the situation a little bit clearer.

Well psql is failing to deal with it *in this case*, as that's what is
doing the \copy in the import/export tool.

In other cases (i.e. the ones where pgAdmin sees the data, such as
results in the query tool), the issue arises because Python and/or
Javascript (and by extension pgAdmin) may barf on data encoded in a
way they don't recognise. That's why the PostgreSQL docs say to only
use ASCII data in SQL_ASCII databases - the behaviour is undefined,
and as a result may either not render properly or may crash or error
on non-ASCII data.

Anyhoo, I expect to have a little time after dinner shortly so I'll
try out the workaround I thought of earlier to see if it helps (I
doubt it'll be a panacea, but it may help in some cases).

By any chance do you have a test case you can share with me that
refuses to export from pgAdmin (using the Import/Export tool)? If so,
I'd appreciate a copy of it to play with.

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

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

https://www.cl.cam.ac.uk/~mgk25/ucs/quotes.html

Smart quotes are not ascii.   They are Unicode, strictly speaking .

We’ve Postgres reject  putting such stuff into the database using anything .

Sent from my iPad

On Jan 8, 2019, at 9:23 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:

Dave, 

Thanks for continuing this discussion, but I think you misunderstand the situation.  I am storing valid non-UTF8 data in a SQL_ASCII encoded postgreSQL database (please re-read what I had previously written). This is why psql has NO problem dealing with it.  This is also why Windows ODBC and .Net applications have NO problem dealing with it.  In fact the most common character that pgAdmin4 crashes on is the Windows smart quote.  So to reiterate, I am using valid non-UTF8 characters in a SQL_ASCII database.  This is a supported configuration for postgreSQL.  The issue seems to be that pgAdmin4 is assuming  UTF8 data and crashing/failing/throwing errors when it encounters invalid UTF8 characters.

I hope I have made the situation a little bit clearer.

Thanks again, 

rik. 

On Tue, Jan 8, 2019 at 12:29 AM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Tue, Jan 8, 2019 at 12:47 AM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Dave,
>
> Thanks for taking the time to respond, but I don't see anywhere that SQL_ASCII is recommended against doing. Here's the documentation listing the supported encoding schemas: https://www.postgresql.org/docs/current/multibyte.html .
>
> The only caveats listed for SQL_ASCII are:
>>
>> 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.

You highlighted it below: "If the client character set is defined as
SQL_ASCII, encoding conversion is disabled, regardless of the server's
character set. Just as for the server, use of SQL_ASCII is unwise
unless you are working with all-ASCII data"

You're using UTF-8 data, not ASCII, which it says is unwise because
conversion won't take place (and consequently, neither will
validation). I don't see how one could read that and not take it as

You are running into exactly that problem; and it's visible when
working with technologies that are strict about following encoding
rules - in this case, psql when pgAdmin shells out to it.

I did think of one possible quick fix this morning which I'll look
into, but as I noted before; it's a workaround, and the real problem
is storing un-validated UTF-8 data in a SQL_ASCII database.

> Or, a reminder that postgreSQL can't help with any conversions you might want to do.
>
> Then there's this:
>>
>> PostgreSQL will allow superusers to create databases with SQL_ASCII encoding even when LC_CTYPE is not C or POSIX. As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.
>
>
> A note that you can currently choose incompatible settings, but probably can't in the future.
>
> And finally there's this bit of advice:
>>
>> If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server's character set. Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data[emphasis mine].
>
>
> Which is just a reiteration of the first caveat, that if you are using SQL_ASCII the database won't perform any conversions on your behalf.
>
> That is hardly a recommendation against using that supported encoding scheme.  The fact that the psql command prompt, among others, works with it without issue, is an indication that the problem lies in pgAdmin4 (and I would guess the reliance of python on UTF8) than an issue with the database itself.  pgAdmin4 needs to check for and more gracefully handle valid postgreSQL data that might happen to be not UTF8 compliant.
>
> Until then, I will have to periodically scan and clean for bad UTF8 data to keep pgAdmin4 (and other JDBC dependent code) happy.  The legacy enterprise .Net applications that depend on it prohibit converting it to UTF8 (or anything else for that matter).
>
> Just my $0.02,
>
> rik.
>
>
> On Mon, Jan 7, 2019 at 1:27 PM Dave Page <dpage@pgadmin.org> wrote:
>>
>> Hi
>>
>> On Mon, Jan 7, 2019 at 11:30 PM richard coleman
>> <rcoleman.ascentgl@gmail.com> wrote:
>> >
>> > Dave,
>> >
>> > I can't speak to Nania's specific issue, but I believe it's a pgAdmin4 specific problem, at least in so far as SQL_ASCII is concerned.  I say this because I can usually work with the data just fine from the psql prompt, but not through pgAdmin4 (or other postgreSQL GUI's like dBeaver that rely on the JDBC connection).  .Net/Windows ODBC drivers and psql command prompt, no problem (as was pgAdmin3 assuming you don't do too much with it beyond select/update/insert).  pgAdmin4, SELECT, export, etc. BOOM! At least until you cleaned  up the offending bytes.
>> >
>> > Just my $0.02.
>>
>> I'm afraid the fundamental problem is that you're using PostgreSQL in
>> a way that the docs specifically recommend against doing, and you're
>> seeing the reason why.
>>
>> pgAdmin 3 and 4 are completely different. In the import/export utility
>> that Nania reported the issue in, pgAdmin doesn't look at the data *at
>> all*. It simply executes \copy in psql, which does all the work. All
>> pgAdmin does is provide connection info and options to psql, based on
>> the selections made in the import/export dialogue, and executes it.
>>
>> In other areas of pgAdmin, like the query tool, it is possible to see
>> similar issues with the same underlying cause, though we've spent a
>> significant amount of time trying to work around all the possible edge
>> cases.
>>
>> pgAdmin 3 implemented import/export itself, using underlying libraries
>> that were far less strict about encoding rules than Python is. That
>> may have been more convenient for this particular issue, but it's a
>> lot worse in many others.
>>
>> As a general thought (and do bear in mind, we've spent significant
>> time and resources on these issues in the past), I'd far rather spend
>> time on new features and actual bugs, than further time on workarounds
>> for things the PostgreSQL docs specifically advise against doing.
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company



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

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

Had a rethink of this.      We had sql ascii databases with non ascii data in it.   When converting that data to utf8 and sticking into Postgres, all that windows extended characters could not be used unless converted to utf8 before hand.

It may be that your case depends 9n the character coding issued by the client as it overrides the database encoding to some extent.

So.. you may want to just give it up and go utf8 anyway,    It sets you up for the future.

Or go get pgadmin 3 from bigsql ...  it will talk to Postgres 10 for sure and I think it talks to Postgres 11.      I use both pgadmin 3 and 4 since there are some things that both do well. 

Sent from my iPad

On Jan 8, 2019, at 5:44 PM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:

Doug, 

Hi.  Is this a typo?

We’ve Postgres reject  putting such stuff into the database using anything .

PostgreSQL allow any text into a column if the database has been set to use SQL_ASCII as the encoding. This was the default encoding on Windows machines, a long long time ago.  All that setting the encoding to SQ:_ASCII means is that postgreSQL won't do any translations between character encoding for you.  We are storing what I believe is Windows-1252 characters in the database.  The immediate problem (at least mine in so far as pgAdmin4 is concerned) is that non-UTF8 characters (like smart quotes) are handled without a problem by psql, .Net, and Windows ODBC (and under pgAdmin3) but cause pgAdmin4 to crash/fail/thow errors.  The only way to get pgAdmin4 to behave is to locate and remove the offending characters.  My guess is that this is due to it's python underpinnings and the expectation that the database would correctly translate the data into valid UTF8.  It is my belief that it should handle such valid postgreSQL data better than it currently does.  At least as well as it's predecessor pgAdmin3 did.

Just my $0.02, 

rik.

On Tue, Jan 8, 2019 at 3:56 PM Doug Easterbrook <doug@artsman.com> wrote:

https://www.cl.cam.ac.uk/~mgk25/ucs/quotes.html

Smart quotes are not ascii.   They are Unicode, strictly speaking .

We’ve Postgres reject  putting such stuff into the database using anything .

Sent from my iPad

On Jan 8, 2019, at 9:23 AM, richard coleman <rcoleman.ascentgl@gmail.com> wrote:

Dave, 

Thanks for continuing this discussion, but I think you misunderstand the situation.  I am storing valid non-UTF8 data in a SQL_ASCII encoded postgreSQL database (please re-read what I had previously written). This is why psql has NO problem dealing with it.  This is also why Windows ODBC and .Net applications have NO problem dealing with it.  In fact the most common character that pgAdmin4 crashes on is the Windows smart quote.  So to reiterate, I am using valid non-UTF8 characters in a SQL_ASCII database.  This is a supported configuration for postgreSQL.  The issue seems to be that pgAdmin4 is assuming  UTF8 data and crashing/failing/throwing errors when it encounters invalid UTF8 characters.

I hope I have made the situation a little bit clearer.

Thanks again, 

rik. 

On Tue, Jan 8, 2019 at 12:29 AM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Tue, Jan 8, 2019 at 12:47 AM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Dave,
>
> Thanks for taking the time to respond, but I don't see anywhere that SQL_ASCII is recommended against doing. Here's the documentation listing the supported encoding schemas: https://www.postgresql.org/docs/current/multibyte.html .
>
> The only caveats listed for SQL_ASCII are:
>>
>> 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.

You highlighted it below: "If the client character set is defined as
SQL_ASCII, encoding conversion is disabled, regardless of the server's
character set. Just as for the server, use of SQL_ASCII is unwise
unless you are working with all-ASCII data"

You're using UTF-8 data, not ASCII, which it says is unwise because
conversion won't take place (and consequently, neither will
validation). I don't see how one could read that and not take it as

You are running into exactly that problem; and it's visible when
working with technologies that are strict about following encoding
rules - in this case, psql when pgAdmin shells out to it.

I did think of one possible quick fix this morning which I'll look
into, but as I noted before; it's a workaround, and the real problem
is storing un-validated UTF-8 data in a SQL_ASCII database.

> Or, a reminder that postgreSQL can't help with any conversions you might want to do.
>
> Then there's this:
>>
>> PostgreSQL will allow superusers to create databases with SQL_ASCII encoding even when LC_CTYPE is not C or POSIX. As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.
>
>
> A note that you can currently choose incompatible settings, but probably can't in the future.
>
> And finally there's this bit of advice:
>>
>> If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server's character set. Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data[emphasis mine].
>
>
> Which is just a reiteration of the first caveat, that if you are using SQL_ASCII the database won't perform any conversions on your behalf.
>
> That is hardly a recommendation against using that supported encoding scheme.  The fact that the psql command prompt, among others, works with it without issue, is an indication that the problem lies in pgAdmin4 (and I would guess the reliance of python on UTF8) than an issue with the database itself.  pgAdmin4 needs to check for and more gracefully handle valid postgreSQL data that might happen to be not UTF8 compliant.
>
> Until then, I will have to periodically scan and clean for bad UTF8 data to keep pgAdmin4 (and other JDBC dependent code) happy.  The legacy enterprise .Net applications that depend on it prohibit converting it to UTF8 (or anything else for that matter).
>
> Just my $0.02,
>
> rik.
>
>
> On Mon, Jan 7, 2019 at 1:27 PM Dave Page <dpage@pgadmin.org> wrote:
>>
>> Hi
>>
>> On Mon, Jan 7, 2019 at 11:30 PM richard coleman
>> <rcoleman.ascentgl@gmail.com> wrote:
>> >
>> > Dave,
>> >
>> > I can't speak to Nania's specific issue, but I believe it's a pgAdmin4 specific problem, at least in so far as SQL_ASCII is concerned.  I say this because I can usually work with the data just fine from the psql prompt, but not through pgAdmin4 (or other postgreSQL GUI's like dBeaver that rely on the JDBC connection).  .Net/Windows ODBC drivers and psql command prompt, no problem (as was pgAdmin3 assuming you don't do too much with it beyond select/update/insert).  pgAdmin4, SELECT, export, etc. BOOM! At least until you cleaned  up the offending bytes.
>> >
>> > Just my $0.02.
>>
>> I'm afraid the fundamental problem is that you're using PostgreSQL in
>> a way that the docs specifically recommend against doing, and you're
>> seeing the reason why.
>>
>> pgAdmin 3 and 4 are completely different. In the import/export utility
>> that Nania reported the issue in, pgAdmin doesn't look at the data *at
>> all*. It simply executes \copy in psql, which does all the work. All
>> pgAdmin does is provide connection info and options to psql, based on
>> the selections made in the import/export dialogue, and executes it.
>>
>> In other areas of pgAdmin, like the query tool, it is possible to see
>> similar issues with the same underlying cause, though we've spent a
>> significant amount of time trying to work around all the possible edge
>> cases.
>>
>> pgAdmin 3 implemented import/export itself, using underlying libraries
>> that were far less strict about encoding rules than Python is. That
>> may have been more convenient for this particular issue, but it's a
>> lot worse in many others.
>>
>> As a general thought (and do bear in mind, we've spent significant
>> time and resources on these issues in the past), I'd far rather spend
>> time on new features and actual bugs, than further time on workarounds
>> for things the PostgreSQL docs specifically advise against doing.
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company



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

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