Oracle version: =============== CREATE OR replace PROCEDURE Item_reco_recordset (p_depositid IN NUMBER, p_b1 IN NUMBER, p_itemset OUT SYS_REFCURSOR) AS BEGIN OPEN p_itemset FOR SELECT * FROM pcwitem WHERE deposittransid = p_depositid AND ( ( Bitand(recostatecode, 1) > 0 AND Bitand(recostatecode, 5) != 0 ) OR ( Bitand(recostatecode, 2) > 0 AND Bitand(recostatecode, 6) != 0 ) OR ( p_b1 = 1 AND Bitand(recostatecode, 3) > 0 AND Bitand(recostatecode, 7) != 0 ) OR ( Bitand(recostatecode, 4) > 0 AND Bitand(recostatecode, 8) != 0 ) OR ( Bitand(recostatecode, 9) > 0 AND Bitand(recostatecode, 10) != 0 ) OR ( Bitand(recostatecode, 11) > 0 AND Bitand(recostatecode, 15) != 0 ) OR ( Bitand(recostatecode, 12) > 0 ) OR ( Bitand(recostatecode, 16) > 0 AND Bitand(recostatecode, 14) != 0 ) OR ( Bitand(recostatecode, 18) > 0 ) OR ( Bitand(recostatecode, 19) > 0 AND Bitand(recostatecode, 20) != 0 ) OR ( Bitand(recostatecode, 21) > 0 ) OR ( Bitand(recostatecode, 22) > 0 ) OR ( Bitand(recostatecode, 23) > 0 AND Bitand(recostatecode, 24) != 0 ) OR ( Bitand(recostatecode, 25) > 0 AND Bitand(recostatecode, 29) != 0 ) OR ( Bitand(recostatecode, 26) > 0 ) OR ( Bitand(recostatecode, 28) > 0 ) OR ( Bitand(recostatecode, 31) > 0 ) OR ( Bitand(recostatecode, 32) > 0 AND Bitand(recostatecode, 61) != 0 ) OR ( Bitand(recostatecode, 39) > 0 ) OR ( Bitand(recostatecode, 40) > 0 AND Bitand(recostatecode, 64) != 0 ) OR ( Bitand(recostatecode, 48) > 0 ) OR ( Bitand(recostatecode, 51) > 0 ) OR ( Bitand(recostatecode, 53) > 0 AND Bitand(recostatecode, 54) != 0 ) OR ( Bitand(recostatecode, 57) > 0 AND Bitand(recostatecode, 58) != 0 ) ); END item_reco_recordset; SQL Server Version (throws error -- bitand not a recognized built-in function): ---------- IF OBJECT_ID('Item_reco_recordset', 'P') IS NOT NULL DROP PROCEDURE Item_reco_recordset; GO CREATE PROCEDURE Item_reco_recordset (@p_depositid FLOAT, @p_b1 FLOAT ) AS BEGIN SET NOCOUNT ON; SELECT * FROM pcwitem WHERE deposittransid = @p_depositid AND ( ( Bitand(recostatecode, 1) > 0 AND Bitand(recostatecode, 5) != 0 ) OR ( Bitand(recostatecode, 2) > 0 AND Bitand(recostatecode, 6) != 0 ) OR ( @p_b1 = 1 AND Bitand(recostatecode, 3) > 0 AND Bitand(recostatecode, 7) != 0 ) OR ( Bitand(recostatecode, 4) > 0 AND Bitand(recostatecode, 8) != 0 ) OR ( Bitand(recostatecode, 9) > 0 AND Bitand(recostatecode, 10) != 0 ) OR ( Bitand(recostatecode, 11) > 0 AND Bitand(recostatecode, 15) != 0 ) OR ( Bitand(recostatecode, 12) > 0 ) OR ( Bitand(recostatecode, 16) > 0 AND Bitand(recostatecode, 14) != 0 ) OR ( Bitand(recostatecode, 18) > 0 ) OR ( Bitand(recostatecode, 19) > 0 AND Bitand(recostatecode, 20) != 0 ) OR ( Bitand(recostatecode, 21) > 0 ) OR ( Bitand(recostatecode, 22) > 0 ) OR ( Bitand(recostatecode, 23) > 0 AND Bitand(recostatecode, 24) != 0 ) OR ( Bitand(recostatecode, 25) > 0 AND Bitand(recostatecode, 29) != 0 ) OR ( Bitand(recostatecode, 26) > 0 ) OR ( Bitand(recostatecode, 28) > 0 ) OR ( Bitand(recostatecode, 31) > 0 ) OR ( Bitand(recostatecode, 32) > 0 AND Bitand(recostatecode, 61) != 0 ) OR ( Bitand(recostatecode, 39) > 0 ) OR ( Bitand(recostatecode, 40) > 0 AND Bitand(recostatecode, 64) != 0 ) OR ( Bitand(recostatecode, 48) > 0 ) OR ( Bitand(recostatecode, 51) > 0 ) OR ( Bitand(recostatecode, 53) > 0 AND Bitand(recostatecode, 54) != 0 ) OR ( Bitand(recostatecode, 57) > 0 AND Bitand(recostatecode, 58) != 0 ) ); END; item_reco_recordset;

I wish to encrypt an Unicode string by using bitwise operators from my client (Dart) app, and send it to my PostgreSQL server where a function will decrypt it by using bitwise operators. But I don't find any info to respect, is it possible? NOTE: with Dart I can convert a string into codes and they can be dealt with bitwise operators.

I have a question: I have two row security policies to restrict the access to a table. The first policy uses an Array-Column "rights". The "rights" column is type 16-bit bit/integer array and each user is assigned an index. If the array has the value 1 at an index, the user with that index can read the line. The policy for looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING (rights[(select array_pos from account where username = current_user)] > 0);

The second policy uses bitstrings with the same logic so for example: Value in the table: 1001010 User1 bit string: 0001000 User2 bit string: 0000100 1001010 & 0001000 = 0001000 -> User1 has access 1001010 & 0000100 = 0000000 -> User2 has no access The policy looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING ((bitstring & (select user_bitstring from account where username = current_user))  B'0'::BIT(16));

Intuitively, I would argue that the second option is more performant as a bit string does not consume a lot of memory and binary operations don't cost much, but if one compares the performance, the array version is even a little faster. How come? Here are the two evaluation plans with the same test data (2 million lines and user has access to half):

Array:
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000526114.16 rows=666667 width=1160) (actual time=0.025..2733.524 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000526106.00 rows=666667 width=1160) (actual time=0.025..2660.927 rows=1000000 loops=1)
         Filter: (rights[$0] > 0)
         Rows Removed by Filter: 1000000
 Planning Time: 0.181 ms
 Execution Time: 2768.561 ms
(9 rows)

Bitstring:
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000266895.60 rows=1000000 width=1160) (actual time=0.026..2759.098 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=7) (actual time=0.013..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000531106.00 rows=1990000 width=1160) (actual time=0.025..2685.145 rows=1000000 loops=1)
         Filter: ((bitstring & $0)  '0000000000000000'::bit(16))
         Rows Removed by Filter: 1000000
 Planning Time: 0.167 ms
 Execution Time: 2794.719 ms
(9 rows)

Some comparison between the two versions (yellow and purple - blue without any policy) x-Axis: Count of Rows in thousand, y-Axis: Execution Time of the query - averaged: And another question: is there a better way to filter access. It should be possible that the rights to a line are changeable. Would it be possible, for example, to build an index that speeds up the filtering process?

I need bit(64) representation of current epoch (the operations I need should be much easier in bits), so SELECT extract('epoch' from CURRENT_TIMESTAMP)::bit(64) But this gives me ERROR: cannot cast type double precision to bit What am I doing wrong here? UPD: In fact, I understood I should do it the other way, but the ERROR itself is still very strange for me.

I'm designing a table to keep track of a value that has to represent the on-off setting of several hundred states. I would expand this to 512, to give room for future growth and be a power of two. So I want a 64 byte = 512 bit column. My problem is that SqlServer bitwise operations don't support right operands larger than int, so there would be no way to access any bits past 32. How can I handle this issue?

Two examples: Table users, each user may have or not have some of ~10 possible permissions (like "can login, can post, can edit, can delete" etc). Table articles, each article may have or not have some of ~10 possible attributes (like "for children", "18+", "less than 10 minutes to read", "long read"). *What is the canonical \ more efficient \ fastest way to store it in the database?* I can think of two ways: **Option 1**: Bit-like kind of column, have column "permissions"\"attributes" and store a value which can be bit-interpreted like "0101100010", i-th bit is the flag for i-th attribute\permission **Option 2**: Relationship. Create table user_permissions, put 10 values with their IDs there, then create table user_permissions_map and keep this many to many (M:N) relationship in this table. I'm a bit afraid of option 2 because it looks like it will require additional queries\lookups in permission_map table each time I need to check user's permission. When with option 1 it's just a column belonging to the user, makes it much simpler to check the permissions.

[Another question I have pertains to just the & over string types](https://dba.stackexchange.com/q/204824/2639) . This question is over the binary-string literals as constructed with B'' or X''. MariaDB supports binary and hex literals, SELECT X'01', B'00000001', X'01' = B'00000001'; | X'01' | B'00000001' | X'01' = B'00000001' | +-------+-------------+---------------------+ | X | X | 1 | * Using X because StackExchange strips the non-printable character. However, interestingly, they're not the same when I pass them through the [Binary-operators](https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html) SELECT X'01' & B'00000001', X'01' & X'01', B'00000001' & B'00000001'; | X'01' & B'00000001' | X'01' & X'01' | B'00000001' & B'00000001' | +---------------------+---------------+---------------------------+ | 0 | 0 | 1 | So if they're treated as bit-strings it doesn't make sense (because the result should be 1. And if they're treated as strings it doesn't make sense because the actual string *is* in fact (BIT-ANDed) & to 1. This shows they don't **"behave as a string in all contexts"**. SELECT X'01' & B'00000001', '01' & '00000001'; +---------------------+-------------------+ | X'01' & B'00000001' | '01' & '00000001' | +---------------------+-------------------+ | 0 | 1 | +---------------------+-------------------+ I see this, [in the doc on hex literals,](https://dev.mysql.com/doc/refman/8.0/en/hexadecimal-literals.html) > For hexadecimal literals, bit operations are considered numeric context, but bit operations permit numeric or binary string arguments in MySQL 8.0 and higher. To explicitly specify binary string context for hexadecimal literals, use a _binary introducer for at least one of the arguments. [MariaDB says](https://mariadb.com/kb/en/library/hexadecimal-literals/) , > Normally, hexadecimal literals are **interpreted as binary string**, where each pair of digits represents a character. When used in a numeric context, they are interpreted as integers. (See the example below). In no case can a hexadecimal literal be a decimal number. But that still doesn't seem to cut it, if anything it makes it worse (now none of the results of & return non-0. SELECT _binary X'01' & _binary B'00000001', _binary X'01' & _binary X'01', _binary B'00000001' & _binary B'00000001'; | _binary X'01' & _binary B'00000001' | _binary X'01' & _binary X'01' | _binary B'00000001' & _binary B'00000001' | +-------------------------------------+-------------------------------+-------------------------------------------+ | 0 | 0 | 0 | Even an explicit cast is wrong, SELECT CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8)); | CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8)) | +--------------------------------------------------------------------------+ | 0 | Another bizarre thing is that they act different when stored on a table, for example, CREATE TABLE g AS SELECT b'00000001' AS value, b'00000001' = 0 AS isequal; SELECT value, value=0, isequal AS wasequal FROM g; +-------+---------+----------+ | value | value=0 | wasequal | +-------+---------+----------+ | | 1 | 0 | +-------+---------+----------+ **Why do binary literals and hex-literals behave different and what are their behaviors?**

Is the behavior of BITAND, (*bitwise and*) defined in MariaDB over strings? SELECT '0011' & '0011', 'ff' & 'ff', 'yy' & 'YY', '5' & '5'; +-----------------+-------------+-------------+-----------+ | '0011' & '0011' | 'ff' & 'ff' | 'yy' & 'YY' | '5' & '5' | +-----------------+-------------+-------------+-----------+ | 11 | 0 | 0 | 5 | +-----------------+-------------+-------------+-----------+ I'm just wondering what is exactly happening here.

PostgreSQL supports [Bit Strings](https://www.postgresql.org/docs/current/static/functions-bitstring.html) , SELECT B'01010101', B'01010101', '01010101'::bit(8), X'CC'; ?column? | ?column? | bit | ?column? ----------+----------+----------+---------- 01010101 | 01010101 | 01010101 | 11001100 SELECT pg_typeof(B'01010101'), pg_typeof(B'01010101'), pg_typeof('01010101'::bit(8)), pg_typeof(X'CC'); pg_typeof | pg_typeof | pg_typeof | pg_typeof -----------+-----------+-----------+----------- bit | bit | bit | bit The type bit is typed such that I can create a function that accepts a bit of any length regardless (variable-length), and return a Bit String of variable-length: CREATE FUNCTION type_test(_x bit) RETURNS bit AS $$ SELECT _x $$ LANGUAGE sql; SELECT type_test(X'CC'), type_test(X'CCCC'); Let's say I want to do an operation though that requires a bit-shifting algorithm, or a scratch pad bigger than the input type, how would I go about declaring that for internal use to the function. Like if I want to create a function that given X'CC' (8 bits) could use a 10 bit scratch pad. This would almost do what I want, CREATE FUNCTION type_test(_x bit) RETURNS bit AS $$ SELECT ('0' || _x || '0')::bit $$ LANGUAGE sql; SELECT type_test(X'55'); -- returns 0 But, it doesn't work because bit is bit(1) so the cast to it truncates the input to a single bit. Given bit(n), how do I create a bit(n+2) to work with.