CODEWITHSUNDEEP
postgresqlbitwise-operatorslong-integerbigint
okila
okila

Reputation: 43

Long numbers in PostgreSQL and bitwise operations

There's a django application using PostgreSQL DB.
Our task is to collect and store MSSQL Server permissions (their count is about 120). It implemented as a long number where each bit is 1 permission.
Thus we need to store a very long number in our PostgreSQL but its MAX numeric value is 64 bits. That's why we store is as a string now. Application creates a SQL query like:

SELECT "mssql_ace"."id", .... "mssql_ace"."permissions",

FROM "mssql_ace"

LEFT OUTER JOIN ... ON ...
...
WHERE (.... AND ("mssql_ace"."permissions" & 1) = 1)

ORDER BY ... ;

The problem now is on WHERE part. We need bitwise operations, it's a way to filter data by permissions that works in the whole app except this. We can't use bitwise operations with string.

We'd tried casting in PostgreSQL: 

1. (CAST("mssql_ace"."permissions" AS BIGINT) & 1)

2. "mssql_ace"."permissions"::BIGINT & 1

And other things:

3. DECIMAL also doesn't support bitwise

We have an idea to divide this big number on two parts. But it needs a lot of work to change inside app. Any other ideas? The main thing we can't change is usage of bitwise operations.

Thank you.

Upvotes: 2

Views: 1831

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247043

I don't know how to integrate that in django, but the logical PostgreSQL data type for this would be bit varying. It allows you to efficiently store long bit strings.

Upvotes: 1

Related Questions