postgresqlcomparisoncomparison-operatorsbigint

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


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


Solution

  • 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.