Postgresql ERROR: operator does not exist: bigint << bigint

Question

My algorithm was working fine but with a new big database, my integers variable exceed the max limit size. (I use the powerset algorithm : https://www.postgresql.org/message-id/20060924054759.GA71934%40winnie.fuhr.org)

So I've decided to change all of my integer to bigint, but now I have a problem on the comparison operator... I don't know how to manage it :

CREATE OR REPLACE FUNCTION powerset(a anyarray)
  RETURNS SETOF anyarray AS
$BODY$
DECLARE
    retval  a%TYPE;
    alower  bigint := array_lower(a, 1);
    aupper  bigint := array_upper(a, 1);
    j       bigint;
    k       bigint;
BEGIN
    FOR i IN 1 .. COALESCE((CAST(1 AS BIGINT) << (aupper - alower + 1)) - 1, 0) LOOP
        retval := '{}';
        j := alower;
        k := i;

        WHILE k > CAST(0 AS BIGINT) LOOP
            IF k & CAST(1 AS BIGINT) = CAST(1 AS BIGINT) THEN
                retval := array_append(retval, a[j]);
            END IF;

            j := j + CAST(1 AS BIGINT);
            k := k >> CAST(1 AS BIGINT);
        END LOOP;

        RETURN NEXT retval;
    END LOOP;

    RETURN;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE STRICT
  COST 100
  ROWS 1000;
ALTER FUNCTION powerset(anyarray)
  OWNER TO postgres;

I've got the error on line :

FOR i IN 1 .. COALESCE((CAST(1 AS BIGINT) << (aupper - alower + 1)) - 1, 0) LOOP

Error 42883 Postgresql ERROR: operator does not exist: bigint << bigint

klin · Accepted Answer · 2023-08-04 10:20:59Z

3

The type of the right operand of bitwise shift operators is integer. Unfortunately, this was not mentioned in the documentation. (The documentation was corrected in Postgres 13)

You should cast the right operand of shift operators to integer:

-- instead of 
-- COALESCE((CAST(1 AS BIGINT) << (aupper - alower + 1)) - 1, 0)
-- use
select COALESCE(1 << (aupper - alower + 1)::int- 1, 0)::bigint

-- instead of
-- k := k >> CAST(1 AS BIGINT);
--- use
k := k >> 1;
-- etc

You can check possible types of operands by querying the system catalog pg_operator, e.g.:

select oprname, oprleft::regtype, oprright::regtype
from pg_operator
where oprname = '<<'
and oprcode::text like '%shl%' -- shift left functions

 oprname | oprleft  | oprright 
---------+----------+----------
 <<      | smallint | integer
 <<      | integer  | integer
 <<      | bigint   | integer
(3 rows)

The above result shows that left operand of the operator << (bitwise shift left) can be smallint, integer or bigint and right operand must be integer.

edited Aug 4, 2023 at 10:20

answered Nov 21, 2017 at 20:43

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quentin T. Over a year ago

Thanks ! What is the difference between int2shl, int4shl, int8shl so ? So I have to do it for all of my algorithm ? Each comparison operator has to been changed in function ?

Collectives™ on Stack Overflow

Postgresql ERROR: operator does not exist: bigint << bigint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related