Translate this MySQL query to PyGreSQL

Question

I'm working on a Ruby app which uses the mysql functions XOR (^) and BIT_COUNT(). However I now need to run the app on Heroku, which runs PyGreSQL.

I can't find any documentation for the bits of PyGreSQL that can help me.

So can anyone translate this mysql query so that it would work when executed on a pygresql database?

SELECT * FROM "photos" WHERE BIT_COUNT(phash ^ 2061756291569501157) <= 15

pygresql gives me the error

ERROR:  operator does not exist: text ^ bigint

thanks.

Shaeshaef

# is bitwise XOR. (See postgresql.org/docs/7.4/static/functions-math.html) I'm not sure there's an exact parallel for bit_count(). – Submediant

Ah, there is. Here you go: #1910869 – Submediant

Helpful hint: refer to it as PostgreSQL. PyGreSQL is just the Python module for Postgres. Unless there's something specific to that module, you'll see more help from the Postgres users. The same users don't refer to it as DBD::Pg for Perl, unless the problem has to do with that module specifically, and not the underlying db/query. – Scrag

The phash also seems to be a text field. – Wagon

Answer 1

SELECT  *
FROM    photos
WHERE   (
        SELECT  SUM(((phash::bigint # 2061756291569501157) >> bit) & 1)
        FROM    generate_series(0, 63) bit
        ) <= 15

Maximomaximum

How would I do this if the integers I was XORing were BIGINT UNSIGNED (> 0, < 2 ^ 64 - 1)? Like 2061756291569501157 and 17418945696623429624 – Shaeshaef

@Shea: unfortunately, PostgreSQL does not support unsigned types. You can check for the last 63 bits this way and add an extra condition to check the sign. – Maximomaximum