I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.
A query that demonstrated the behaviour is this:
INSERT INTO "TestTable" ("Input") VALUES (decode('74657374', 'hex'))
74657374 is hexadecimal for ‘test’. In one client, ‘test’ is inserted into the “Input” field, whether that field is text/varchar or bytea. That is the behaviour I desire. In another client, ‘x74657374’ is inserted into the “Input” field, whether it is text/varchar or bytea. This string is the postgresql literal representation of the bytea bytes of ASCII ‘test’. But the sql literal syntax itself is inserted. I do not desire this behaviour.
I use a single piece of hand-written SQL, the bytea value only occurs “within” the query (if column “Input” has type Text then none of literals nor the receiving column have bytea type), and it seems unlikely to me that either client is parsing then rebuilding the query. Therefore it seems that the difference must be happening on the server where the query is executed. That means there must be some connection specific configuration setting which is altering the server behaviour.
Can anyone please tell me what connection specific settings could be altering this behaviour?
I know the queries are really behaving differently and it is not a display issue etc., because I can see the rows in the same table having different values (‘test’ and ‘x74657374’). I have tried various alternative bytea handling methods, but they are all affected by this problem. For those who are interested, the “good” client is pgAdminIII and the “bad” client is Ruby PG gem. Though for the reason I gave above I believe there must be some built-in feature of postgresql supporting this behaviour.