Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- random bitstrings of all legal lengths for bigint
CREATE TABLE t AS
SELECT i, ltrim(i::bit(64)::text, '0')::varbit AS b
FROM   generate_series (1,100000) g
, LATERAL (SELECT (random() * 2^(g%63))::int8) AS x(i);
100000 rows affected

CREATE OR REPLACE FUNCTION varbit2bigint2(b varbit)
  RETURNS bigint AS
$func$
SELECT OVERLAY(bit(64) '0' PLACING b FROM 65 - bit_length(b))::bigint
$func$  LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION varbit2bigint1(b varbit)
  RETURNS bigint AS
$func$
  SELECT CASE bit_length($1)
   WHEN  1 THEN $1::bit(1)::bigint
   WHEN  2 THEN $1::bit(2)::bigint
   WHEN  3 THEN $1::bit(3)::bigint
   WHEN  4 THEN $1::bit(4)::bigint
   WHEN  5 THEN $1::bit(5)::bigint
   WHEN  6 THEN $1::bit(6)::bigint
   WHEN  7 THEN $1::bit(7)::bigint
   WHEN  8 THEN $1::bit(8)::bigint
   WHEN  9 THEN $1::bit(9)::bigint
   WHEN 10 THEN $1::bit(10)::bigint
   WHEN 11 THEN $1::bit(11)::bigint
   WHEN 12 THEN $1::bit(12)::bigint
   WHEN 13 THEN $1::bit(13)::bigint
   WHEN 14 THEN $1::bit(14)::bigint
   WHEN 15 THEN $1::bit(15)::bigint
   WHEN 16 THEN $1::bit(16)::bigint
   WHEN 17 THEN $1::bit(17)::bigint
   WHEN 18 THEN $1::bit(18)::bigint
   WHEN 19 THEN $1::bit(19)::bigint
   WHEN 20 THEN $1::bit(20)::bigint
   WHEN 21 THEN $1::bit(21)::bigint
   WHEN 22 THEN $1::bit(22)::bigint
   WHEN 23 THEN $1::bit(23)::bigint
   WHEN 24 THEN $1::bit(24)::bigint
   WHEN 25 THEN $1::bit(25)::bigint
   WHEN 26 THEN $1::bit(26)::bigint
   WHEN 27 THEN $1::bit(27)::bigint
   WHEN 28 THEN $1::bit(28)::bigint
   WHEN 29 THEN $1::bit(29)::bigint
   WHEN 30 THEN $1::bit(30)::bigint
   WHEN 31 THEN $1::bit(31)::bigint
   WHEN 32 THEN $1::bit(32)::bigint
   WHEN 33 THEN $1::bit(33)::bigint
   WHEN 34 THEN $1::bit(34)::bigint
   WHEN 35 THEN $1::bit(35)::bigint
   WHEN 36 THEN $1::bit(36)::bigint
   WHEN 37 THEN $1::bit(37)::bigint
   WHEN 38 THEN $1::bit(38)::bigint
   WHEN 39 THEN $1::bit(39)::bigint
   WHEN 40 THEN $1::bit(40)::bigint
   WHEN 41 THEN $1::bit(41)::bigint
   WHEN 42 THEN $1::bit(42)::bigint
   WHEN 43 THEN $1::bit(43)::bigint
   WHEN 44 THEN $1::bit(44)::bigint
   WHEN 45 THEN $1::bit(45)::bigint
   WHEN 46 THEN $1::bit(46)::bigint
   WHEN 47 THEN $1::bit(47)::bigint
   WHEN 48 THEN $1::bit(48)::bigint
   WHEN 49 THEN $1::bit(49)::bigint
   WHEN 50 THEN $1::bit(50)::bigint
   WHEN 51 THEN $1::bit(51)::bigint
   WHEN 52 THEN $1::bit(52)::bigint
   WHEN 53 THEN $1::bit(53)::bigint
   WHEN 54 THEN $1::bit(54)::bigint
   WHEN 55 THEN $1::bit(55)::bigint
   WHEN 56 THEN $1::bit(56)::bigint
   WHEN 57 THEN $1::bit(57)::bigint
   WHEN 58 THEN $1::bit(58)::bigint
   WHEN 59 THEN $1::bit(59)::bigint
   WHEN 60 THEN $1::bit(60)::bigint
   WHEN 61 THEN $1::bit(61)::bigint
   WHEN 62 THEN $1::bit(62)::bigint
   WHEN 63 THEN $1::bit(63)::bigint
   WHEN 64 THEN $1::bit(64)::bigint
   ELSE NULL::bigint
  END
$func$  LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION varbit2bigint3(b varbit)
  RETURNS bigint AS
$func$
SELECT lpad(b::text, 64, '0')::bit(64)::bigint
$func$  LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION varbit2bigint4(b varbit)
  RETURNS bigint AS
$func$
SELECT (bit(64) '0' || b << bit_length(b))::bit(64)::bigint
$func$  LANGUAGE SQL IMMUTABLE;
SELECT *
,  varbit2bigint1(b) AS f1
,  varbit2bigint2(b) AS f2
,  varbit2bigint3(b) AS f3
,  varbit2bigint3(b) AS f4
FROM t
LIMIT 10;


i
b
f1
f2
f3
f4


1
1
1
1
1
1

3
11
3
3
3
3

0

null
0
0
0

13
1101
13
13
13
13

29
11101
29
29
29
29

23
10111
23
23
23
23

113
1110001
113
113
113
113

178
10110010
178
178
178
178

456
111001000
456
456
456
456

746
1011101010
746
746
746
746


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT varbit2bigint2(b)        -- Execution time: 33.687 ms
FROM t;


QUERY PLAN


Seq Scan on t (actual rows=100000 loops=1)

Planning Time: 0.061 ms

Execution Time: 31.859 ms


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT varbit2bigint1(b)        -- Execution time: 53.901 ms
FROM t;


QUERY PLAN


Seq Scan on t (actual rows=100000 loops=1)

Planning Time: 0.578 ms

Execution Time: 48.529 ms


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT varbit2bigint3(b)        -- Execution time: 103.931 ms
FROM t;


QUERY PLAN


Seq Scan on t (actual rows=100000 loops=1)

Planning Time: 0.088 ms

Execution Time: 104.304 ms


EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT varbit2bigint3(b)        -- Execution time: 103.730 ms
FROM t;


QUERY PLAN


Seq Scan on t (actual rows=100000 loops=1)

Planning Time: 0.095 ms

Execution Time: 97.314 ms
i	b	f1	f2	f3	f4
1	1	1	1	1	1
3	11	3	3	3	3
0		null	0	0	0
13	1101	13	13	13	13
29	11101	29	29	29	29
23	10111	23	23	23	23
113	1110001	113	113	113	113
178	10110010	178	178	178	178
456	111001000	456	456	456	456
746	1011101010	746	746	746	746