Postgres maximum value for BIGINT

Question

Is it possible to get the maximum value for BIGINT type without hardcoding it?

I know that the limit is well known, however I wouldn't like to hardcode it.

Answer 1

You can use pg_column_size, it'll give you bytes size of bigint. Using it you can get its max and min sizes:

select  (2^(8*pg_column_size(1::bigint)-2))::bigint << 1 as min_bigint_value;
select  -(((2^(8*pg_column_size(1::bigint)-2))::bigint << 1)+1) as max_bigint_value;

So if somehow in the future bigint max value will change (very very very unlikely) your code will still works if you will rely on these calculated values.

You can write functions for convenience:

CREATE OR REPLACE FUNCTION "get_max_bigint_value"() RETURNS BIGINT as $$
DECLARE
    max_bigint_value BIGINT;
BEGIN
    SELECT  -(((2^(8*pg_column_size(1::BIGINT)-2))::BIGINT << 1)+1) INTO max_bigint_value;
    RETURN max_bigint_value;
END
$$ LANGUAGE "plpgsql";

CREATE OR REPLACE FUNCTION "get_min_bigint_value"() RETURNS BIGINT as $$
DECLARE
    min_bigint_value BIGINT;
BEGIN
    SELECT  (2^(8*pg_column_size(1::bigint)-2))::bigint << 1 INTO min_bigint_value;
    RETURN min_bigint_value;
END
$$ LANGUAGE "plpgsql";

and then:

SELECT get_min_bigint_value();
SELECT get_max_bigint_value();

Answer 2

The "ideal" solution would be to create some C functions that just expose the relevant defines (below). But assuming that you'd rather have something in SQL, I think the best you'll be able to do is:

CREATE OR REPLACE FUNCTION bigint_min() RETURNS bigint LANGUAGE sql AS 'SELECT 1::bigint<<63';
CREATE OR REPLACE FUNCTION bigint_max() RETURNS bigint LANGUAGE sql AS 'SELECT ~bigint_min()';
CREATE OR REPLACE FUNCTION int_min() RETURNS int LANGUAGE sql AS 'SELECT 1<<31';
CREATE OR REPLACE FUNCTION int_max() RETURNS int LANGUAGE sql AS 'SELECT ~int_min()';

These are what's defined at the C level. bigint uses PG_INT64_*:

#define PG_INT8_MIN     (-0x7F-1)
#define PG_INT8_MAX     (0x7F)
#define PG_UINT8_MAX    (0xFF)
#define PG_INT16_MIN    (-0x7FFF-1)
#define PG_INT16_MAX    (0x7FFF)
#define PG_UINT16_MAX   (0xFFFF)
#define PG_INT32_MIN    (-0x7FFFFFFF-1)
#define PG_INT32_MAX    (0x7FFFFFFF)
#define PG_UINT32_MAX   (0xFFFFFFFFU)
#define PG_INT64_MIN    (-INT64CONST(0x7FFFFFFFFFFFFFFF) - 1)
#define PG_INT64_MAX    INT64CONST(0x7FFFFFFFFFFFFFFF)
#define PG_UINT64_MAX   UINT64CONST(0xFFFFFFFFFFFFFFFF)

Answer 3

Just for fun: Building on alexpods' answer, we could define a "generic" function (assuming two's complement) for all postgres integers:

create or replace function
  minint(a anyelement)
  returns anyelement
  immutable
  language sql
as $$
  select ((a - a) - 1) << (8 * pg_column_size(a) - 1);
$$
;

create or replace
  function maxint(a anyelement)
  returns anyelement
  immutable
  language sql
as $$
  select ~minint(a)
$$
;

Usage:

select
  minint(0::smallint)
  , maxint(0::smallint)
  , minint(0::int)
  , maxint(0::int)
  , minint(0::bigint)
  , maxint(0::bigint)
;

Result:

 minint | maxint |   minint    |   maxint   |        minint        |       maxint        
--------+--------+-------------+------------+----------------------+---------------------
 -32768 |  32767 | -2147483648 | 2147483647 | -9223372036854775808 | 9223372036854775807
(1 row)

Answer 4

You can reach it in ~100ms and use in code or save to temporary table - here's example of more or less fast way to get the max value to raise it:

do
$$
declare
 i bigint =2;
 r record;
begin
  begin
  for r in 2::int..999::int loop
    i=power(2,r);
    raise info '%', i::text;
  end loop;
  exception when others then raise info '%', 'fast roll on (power to 2) ended - switching to slow (multiplying by 2)'||i::text;
  end;
  begin
  for r in 2::int..999::int loop
    i=i*2;
    raise info '%', i::text;
  end loop;
  exception when others then raise info '%', 'max reached:'||(1+(i-1)*2)::text;
  end;
end;
$$
;