PostgreSQL: Constant for smallint maximum value?

Question

Does PostgreSQL have a constant (like MAXFLOAT in Objective-C) for the maximum value that a smallint can be?

I know the PostgreSQL numeric types documentation says it's 32767, but I'd rather use a constant than hard coding a value that could change.

I'm using this number to prevent an error when incrementing a smallint, e.g.:

UPDATE populations
SET count = count + 1
WHERE city_id = 3
AND count < 32767;

I'm pretty sure the size of `smallint` is specified by the SQL standard, so it is vanishingly unlikely that it will change. You might still want a constant to make the intention clear, in which case Neil McGuigan's answer would be sufficient. — IMSoP, May 09 '14 at 16:48

score 1 · Answer 1 · answered May 08 '14 at 18:44

1

Create it:

create function MAX_SMALLINT() returns smallint immutable language sql as '
  select 32767::smallint;
';

Use it:

UPDATE populations
SET count = count + 1
WHERE city_id = 3
AND count < MAX_SMALLINT();

answered May 08 '14 at 18:44

Neil McGuigan

43,981
12
119
145

That's nice but is still hard-coded. What if PostgreSQL changes the max? – ma11hew28 May 08 '14 at 19:06
then you alter your function body. I wouldn't call that hard coding. – Neil McGuigan May 08 '14 at 19:24

score 1 · Answer 2 · answered May 09 '14 at 16:37

1

In extension of Neil's suggestion, you could use this:

create function MAX_SMALLINT() returns smallint immutable language sql as '
    select ((1 << ((pg_column_size(1::smallint) << 3) - 1)) - 1)::smallint;
';

But honestly, I don't believe smallint will ever be anything else than 2 bytes in Postgres.

answered May 09 '14 at 16:37

Cito

4,942
27
29

Funnily this works for smallint, but not for integer: if I replace `smallint` by `integer`, it shows `SQL Error [22003]: ERROR: integer out of range` :-( – Jan Katins Mar 10 '17 at 18:11

PostgreSQL: Constant for smallint maximum value?

2 Answers2