From: Craig Ringer <craig@postnewspapers.com.au>
To: Bryce Nesbitt <bryce2@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Why don't I get a LATIN1 encoding here with SET ENCODING?
Date: Wed, 04 Nov 2009 12:25:41 +0800
Message-ID: <4AF10245.3050707@postnewspapers.com.au> (raw)
In-Reply-To: <4AF0FAF9.8080306@obviously.com>
References: <4AF0D531.5080309@obviously.com>
<4AF0E70F.1030201@postnewspapers.com.au>
<4AF0F6A7.7070505@obviously.com>
<4AF0F949.3060807@postnewspapers.com.au>
<4AF0FAF9.8080306@obviously.com>
Bryce Nesbitt wrote:
> Craig Ringer wrote:
>> Yes - but you are *not* presenting a Latin-1 character. You're
>> presenting four Latin-1 characters:
>>
>> '\', '3', '7', '5'
> Well, then I have a different question. If I can view a bytea column as
> so:
>
>> select object from context_objects where context_key=100041;
> object
> -----------------------------
> \005\007\021\007Article\003
> (1 row)
>
> How can I modify the data and put it back into the column?
Presuming the working environment:
CREATE TABLE context_objects(
id serial primary key,
object bytea not null
);
-- to avoid E'' and double-escaping
SET standard_conforming_strings = on;
INSERT INTO context_objects (object)
VALUES ( '\005\007\021\007Article\003' ), ( 'test\375' );
I'd just SELECT the `object' value, receiving it in escape form from the
PostgreSQL protocol:
SELECT object FROM context_objects
object
-----------------------------
\005\007\021\007Article\003
... which is a string of 27 characters in the local client_encoding. So
in Perl (or whatever) I'd have a string of length 27:
\005\007\021\007Article\003
I'd then convert that by parsing the escape sequences. (As Perl uses \0
as the octal escape prefix instead of just \, it's necessary to write a
simple loop for that. I don't do Perl voluntarily and haven't used it
for a while so I won't write one inline here, but it should be easy enough.)
Once you have the byte string (length 12 bytes) you manipulate it
however you want, then convert it back to octal escape form for sending
to the Pg server.
client_encoding has no effect on any of this so long as you're using a
client encoding that preserves the lower 128 characters, ie basically
anything except shift-jis.
Now, if your 'object' is actually a string in a single known text
encoding, not just a sequence of bytes, then another approach is
possible. First, stop using `bytea' for text. If the byte sequences are
all known to be latin-1 encoded text, for example, use:
ALTER TABLE context_objects ALTER COLUMN object TYPE text
USING ( convert_from(object, 'latin-1'));
--
-- Now retrieve the rows, which will be returned as text in
-- the client_encoding. Note that \375 is ý in latin-1.
--
testdb=> SELECT object FROM context_objects;
object
-----------------------------
\x05\x07\x11\x07Article\x03
testý
(2 rows)
(Note: If they're all of different encodings, but you know the
encodings, you can make the encoding param of convert_from a column
reference instead).
Now you have 'object' as server-side utf-8 encoded text that'll be
automatically converted to and from the specified client_encoding . If
you want to get the raw server-side-encoded byte sequence you can always
cast to bytea in your query to get the utf-8 byte sequence in octal
escape form:
testdb=> SELECT object::bytea FROM context_objects;
object
-----------------------------
\005\007\021\007Article\003
test\303\275
... but usually you'd just let the DB
> I'm trying to track down while a ten year old system no longer works
> after a Perl DBI upgrade.
Is it just Perl DBI? Or is it also Pg?
Could it be related to the standards_conforming_strings change that
affects string escaping ?
--
Craig Ringer
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, bryce2@obviously.com
Subject: Re: Why don't I get a LATIN1 encoding here with SET ENCODING?
In-Reply-To: <4AF10245.3050707@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox