I run a Postgres 9.2.2 server locally using the Postgres App with ssmode=disable. All the tables are created like they should be and SELECT queries work as expected.
When I create a Postgres instance on Heroku (the server is 9.1.6 according to psql, ssmode=require), everything works fine, except that a query returns no results, where the local postgres instance does.
The specific query is
SELECT * FROM "captcha" WHERE "cid" = $1 LIMIT $2
and cid/$1 is of type character varying(20).
When connecting to the remote instance via psql and executing the query manually, the rows were returned as expected. I just don't understand what could possibly cause that different behavior.
The primary suspect would be the driver that I use bmizerany/pq, but could there be other error sources for this behavior?
UPDATE:
I tried it with a plain query, same result: No rows in result set
SELECT * FROM "captcha" WHERE "cid" = 'JQRPm6qRpYukXCiPUpHZ' LIMIT 1
UPDATE 2:
It has something todo with the bytea field, the following snippet illustrates the problem when executing on those 2 PG versions https://gist.github.com/eaigner/5004468
The problem is, that Heroku uses a non-default bytea_output. So a workaround to deal with this was
SET bytea_output = 'hex';
before running the query
Try using PREPARE/EXECUTE. pq uses the 'extended' query protocol which uses bind rather than the 'simple' protocol that is just text, so that'll model the interaction better.
Please file a bug.