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

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_ASCIIis concerned.  I say this because I can usually work with the data just fine from the psql prompt, but not
throughpgAdmin4 (or other postgreSQL GUI's like dBeaver that rely on the JDBC connection).  .Net/Windows ODBC drivers
andpsql 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