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:34:30 +0800
Message-ID: <4AF10456.8070806@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?
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
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: <4AF10456.8070806@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