Re: Why don't I get a LATIN1 encoding here with SET ENCODING?

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