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
--------------------------0507110741727469636c650374657374fd
(2 rows)
craig=> SELECT encode( object, 'base64' ) FROM context_objects; encode
------------------BQcRB0FydGljbGUDdGVzdP0=
(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
