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
--------------------------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