Message-ID: <4AF10456.8070806@postnewspapers.com.au>
Date: Wed, 04 Nov 2009 12:34:30 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.21 (X11/20090409)
MIME-Version: 1.0
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?
References: <4AF0D531.5080309@obviously.com>
	<4AF0E70F.1030201@postnewspapers.com.au>
	<4AF0F6A7.7070505@obviously.com>
	<4AF0F949.3060807@postnewspapers.com.au>
	<4AF0FAF9.8080306@obviously.com>
In-Reply-To: <4AF0FAF9.8080306@obviously.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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?

Oh: you might also find the encode(...) function useful. From :

http://www.postgresql.org/docs/current/static/functions-string.html

encode(data bytea, type text) returns text
Encode binary data to different representation. Supported types are:
base64, hex, escape. Escape merely outputs null bytes as \000 and
doubles backslashes.

so rather than struggling through Pg's octal-escaped strings you can ask
for them in hex or base64 form, eg:


craig=> \d context_objects
                         Table "public.context_objects"
 Column |  Type   |                          Modifiers

--------+---------+--------------------------------------------------------------
 id     | integer | not null default
nextval('context_objects_id_seq'::regclass)
 object | bytea   | not null
Indexes:
    "context_objects_pkey" PRIMARY KEY, btree (id)

craig=> select * from context_objects;
 id |           object
----+-----------------------------
  1 | \005\007\021\007Article\003
  2 | test\375
(2 rows)

craig=> SELECT encode( object, 'hex' ) FROM context_objects;
          encode
--------------------------
 0507110741727469636c6503
 74657374fd
(2 rows)

craig=> SELECT encode( object, 'base64' ) FROM context_objects;
      encode
------------------
 BQcRB0FydGljbGUD
 dGVzdP0=
(2 rows)


Both the hex-string and base64 forms are trivial to convert to and from
a byte string in Perl. You can use the matching 'decode' function when
updating / inserting data, eg:

  UPDATE context_objects
  SET object = decode('BQcRB0FydGljbGUD', 'base64')
  WHERE id = 2;

--
Craig Ringer