I have UTF8 data stored in Postgres. I can query it with a Perl script and display it properly. When I use the Postgres client, it displays like the encoding is off. I'll see characters displayed like \u0087\u0081.

From inside the database:

=> \encoding

In the shell (I manually set LANGUAGE as I read elsewhere that might be a fix.):


This is in Ubuntu 16.04.

What setting have I missed?

Edited to add code populating psql:


use strict;
use warnings;
use DBI;

### Source DB setup
my $maria_database="srcdb";
my $maria_user = "";
my $maria_password = "";
my $maria_host= "localhost";

my $mariadbh = DBI->connect("dbi:mysql:database=$maria_database;host=$maria_host","$maria_user","$maria_password",{AutoCommit=>1,RaiseError=>1,PrintError=>0,mysql_enable_utf8=>1});

my $mysrcquery = "SELECT fields from sourcetable limit 2;";
my $src = $mariadbh->prepare($mysrcquery);

### Destination DB setup
my $postgresql_database="desdb";
my $postgresql_user="";
my $postgresql_password="";
my $postgresql_host="localhost";

my $dbh = DBI->connect("DBI:Pg:dbname=$postgresql_database; host=$postgresql_host", "$postgresql_user", "$postgresql_password", {AutoCommit => 0,pg_enable_utf8 => 1});

my $pginsertquery = "insert into desttable (fields) VALUES (?)";
my $pginsert = $dbh->prepare($pginsertquery);

my ($col0);
$src->bind_columns(undef, \$col0 );

### Loop through results and insert in to psql
  print $col0;

$dbh-> disconnect or warn "Disconnection failed: DBI::errstr\n";
$mariadbh-> disconnect or warn "Disconnection failed: DBI::errstr\n";

Test sqldump:

-- PostgreSQL database dump

-- Dumped from database version 9.6.1
-- Dumped by pg_dump version 9.6.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: test; Type: TABLE; Schema: public; Owner: testuser

    testdata text

ALTER TABLE test OWNER TO testuser;

-- Name: test id; Type: DEFAULT; Schema: public; Owner: testuser

ALTER TABLE ONLY test ALTER COLUMN id SET DEFAULT nextval('test_id_seq'::regclass);

-- Data for Name: test; Type: TABLE DATA; Schema: public; Owner: testuser

COPY test (testdata) FROM stdin;

-- PostgreSQL database dump complete
  • Can you show us the Perl script to insert the data into the database? My assumption is that perl is inserting non-utf8 data (because working with utf8 in perl is a PITA). That PostgreSQL assumes it's utf8, but that the terminal has no idea of how to display it, becuase it's not utf8. Feb 14, 2017 at 19:00
  • Unset pg_enable_utf8. This should never bet set to 1. If your client encoding is right it'll do that anyway. This is making the driver assume (needlessly) that everything is stored in utf8, and that the client encoding is utf8. This is a bad assumption and can sting you and may be what's happening. Feb 14, 2017 at 19:45
  • Same thing with mysql_enable_utf8 these flags are forcing the driver to make assumptions that it probably should not be making. You're setting SvUTF_on on everything and wondering why all these assumptions don't hold true across multiple databases. And we still have no idea what's in MySQL. Feb 14, 2017 at 19:50
  • Edited to add test sql dump. That was created with mysql_enable_utf8 enabled (because without it my Perl script won't even display the data) but without pg_enable_utf8. The included record should display, "приветствую" and it does so if I extract it with a Perl script without pg_enable_utf8.
    – thatguy
    Feb 14, 2017 at 21:50
  • 1
    Hi Evan. So sorry for my slow response. It turned out to be an issue with a proprietary module used in the Perl script that I didn't include as I sanitized the code that I posted. It was double encoding the data before inserting in to Postgres. I really appreciate your help. What's the appropriate way for me to give you credit for your effort to help me and to not mislead others if they stumble across this? Regardless, thank you.
    – thatguy
    Feb 25, 2017 at 3:09

1 Answer 1


\u0087 (END OF SELECTED AREA) and \u0081 are control characters in the LATIN-1 block. Displaying them as \u0087\u0081 is in fact the convention, and it seems to be what your terminal is doing.

Can you show us the Perl script to insert the data into the database? My assumption is that Perl is inserting non-utf8 data because working with utf8 in perl is a PITA. That PostgreSQL assumes it's utf8 and that the terminal has no idea of how to display it, becuase it's

  • not really utf8, just marked up as utf8.
  • or, you have legitimate control characters encoded in your utf8.

Be careful if you used utf8::upgrade or the like which sets the flag without decoding from the original format.


Update DBD::Pg, DBD::mysql

First update both DBD::Pg, and DBD::mysql

cpan DBD::Pg DBD::mysql 

Bug in DBD::mysql

Reviewing change logs, DBD::mysql has always had a bug, you may even want to install the development version (DBD-mysql-4.041_01)..

2016-12-12 Patrick Galbraith, Michiel Beijen, DBI/DBD community (4.041_1)
* Unicode fixes: when using mysql_enable_utf8 or mysql_enable_utf8mb4,
  previous versions of DBD::mysql did not properly encode input statements
  to UTF-8 and retrieved columns were always UTF-8 decoded regardless of the
  column charset.
  Fix by Pali Rohár.
  Reported and feedback on fix by Marc Lehmann
  Also, the UTF-8 flag was not set for decoded data:

Who knows what the hell they're doing there. Unicode is complex and it's not surprising that MySQL developers and users don't understand it.

Unsetting assumptions

I would unset pg_enable_utf8 and mysql_enable_utf8. Pretend these options exist. You're force-setting a utf8 flag on the internal variable. You probably don't want to do that. You clearly can't ensure everything is utf8, or it'd be working properly. Time to remove assumptions. Not create more assumptions.

We need a sample case. You claim foreign characters are displaying as Unicode escape sequences, but you only present control-characters (which can't be displayed). Perhaps you can simply this whole thing, and dump the table pg_dump -t <Table> or copy the problematic rows into a temp table and dump that. Then we can review that data and see what we've got.

  • Thanks Evan. I'm pulling data from a MariaDB instance to insert in to Postgres. The results of the MariaDB select query didn't display correctly in the terminal until I added, "mysql_enable_utf8=>1" to the connect string. My test script does a select query against Postgres and displays properly through STDOUT. It's just the Postgres client that doesn't display the data as I would expect. The sample characters I posted were just a small subset. The English character I am trying to store display correctly. Foreign language characters are displaying with the \u#### the PG client.
    – thatguy
    Feb 14, 2017 at 19:32
  • DBD::Pg assumes client_encoding is set to utf8. Have you verified this is so? What version of DBD::Pg are you using, and what version of PostgreSQL? And, what is your DBD::Pg version. run this perl -MDBD::Pg\ 99999 -e1. Feb 14, 2017 at 19:43
  • DBD::mysql is 4.0330
    – thatguy
    Feb 14, 2017 at 19:49
  • DBD::Pg is 3.5.3. I've used this command to make sure client_encoding was set: set client_encoding to 'UTF8';. Postgres version = PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit
    – thatguy
    Feb 14, 2017 at 19:55
  • Where did you use that command? Inside of perl after you connected? Or through psql? Feb 14, 2017 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.