1

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.

asthasr
  • 9,125
  • 1
  • 29
  • 43
user545139
  • 935
  • 11
  • 27
  • `#` is bitwise XOR. (See http://www.postgresql.org/docs/7.4/static/functions-math.html) I'm not sure there's an exact parallel for `bit_count()`. – asthasr Mar 28 '11 at 13:20
  • Ah, there is. Here you go: http://stackoverflow.com/questions/1910369/total-number-of-1s-in-a-postgres-bitmask – asthasr Mar 28 '11 at 13:22
  • 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. – vol7ron Mar 28 '11 at 13:32
  • The phash also seems to be a text field. – jkj Mar 28 '11 at 15:18

1 Answers1

2
SELECT  *
FROM    photos
WHERE   (
        SELECT  SUM(((phash::bigint # 2061756291569501157) >> bit) & 1)
        FROM    generate_series(0, 63) bit
        ) <= 15
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • How would I do this if the integers I was XORing were BIGINT UNSIGNED (> 0, < 2 ^ 64 - 1)? Like 2061756291569501157 and 17418945696623429624 – user545139 Mar 28 '11 at 16:52
  • @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. – Quassnoi Mar 28 '11 at 16:56