postgresql BIGINT 的 Postgres 最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28960478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres maximum value for BIGINT
提问by iggy
Is it possible to get the maximum value for BIGINTtype without hardcoding it?
是否有可能在BIGINT没有硬编码的情况下获得类型的最大值？
I know that the limit is well known, however I wouldn't like to hardcode it.
我知道限制是众所周知的，但是我不想对其进行硬编码。
采纳答案by alexpods
You can use pg_column_size, it'll give you bytes size of bigint. Using it you can get its max and min sizes:
你可以使用pg_column_size，它会给你 bigint 的字节大小。使用它你可以得到它的最大和最小尺寸：
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.
因此，如果将来 bigint 最大值会发生变化（非常非常非常不可能），如果您依赖这些计算值，您的代码仍然可以工作。
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();
回答by Jim Nasby
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:
“理想”的解决方案是创建一些仅公开相关定义的 C 函数（如下）。但假设你更喜欢 SQL 中的一些东西，我认为你能做的最好的是：
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_*:
这些是在 C 级别定义的内容。bigint 使用 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)
回答by Vao Tsun
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:
您可以在大约 100 毫秒内达到它并在代码中使用或保存到临时表 - 这是获得最大值以提高它的或多或少快速方法的示例：
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;
$$
;
