PostgreSQL - Smallint overflowing when creating index on multiple columns. Is this a bug?

Question

I was having an issue inserting the value 32767 into a smallint column in Postgres, which would yield the error smallint out of range. This was odd because I could do:

SELECT 32767::int2;

Which would work just fine. After a little hair pulling, I finally tracked this down to an index on the column in question. First, here's the schema (Well, not really but I've simplified this down to a repro case):

CREATE TABLE Test
(
  id uuid NOT NULL,
  cooktime smallint,
  preptime smallint,
  CONSTRAINT test_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

I now create the following index:

CREATE INDEX idx_test_totaltime
  ON Test
  USING btree
  ((cooktime + preptime) );

Next, I try to create the following row:

INSERT INTO Test (CookTime, PrepTime, Id)
VALUES (
  (32767)::int2,
  (10)::int2,
  (E'fd47dc1e-c3c6-42c1-b058-689e926a72a4')::uuid
);

I get the error:

ERROR: smallint out of range SQL state: 22003

It seems that idx_test_totaltime is expecting a maximum value of int2, even though the index is applied on the sum of two smallints.

Is this a Postgres bug, or am I missing something simple? Is there a way to work around this limitation, or would I need to make these columns int4 and use a CHECK constraint to limit each value to 32767? I'm using Postgres 9.0.0 (Yes, I need to upgrade!) but I've created a SQL Fiddle which demonstrates this error on 9.1.4.

mu is too short · Accepted Answer · 2012-08-05 05:37:29Z

6

Your problem is that int2 + int2 is another int2 so the expression in your index, (cooktime + preptime), overflows for (32767, 10). You can get around this problem with a bit of casting in the index expression:

CREATE INDEX idx_test_totaltime
  ON Test
  USING btree
  ((cooktime::int4 + preptime::int4));

You only need one of the casts but using both doesn't hurt.

answered Aug 5, 2012 at 5:37

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mike Christensen Over a year ago

That works. However, I've noticed if you do SELECT * FROM Test WHERE CookTime + PrepTime > 100 then it will not use the index. You have to specify WHERE CookTime::Int4 + PrepTime::Int4 > 100 instead. Now I have to update my search code :)

mu is too short Over a year ago

@MikeChristensen: That's a good spot on the index not getting used. I'd recommend just forget about all the int2 business and use int for the column with appropriate CHECK constraints.

Frank Heikens Over a year ago

Cast the result of the calculation to an INT2, not the columns in the index.

mu is too short Over a year ago

@FrankHeikens: You can't cast the result in the index, the overflow will happen before the (cooktime + preptime)::int4 cast. Or am I misunderstanding what you're trying to say?

Frank Heikens Over a year ago

Ok, if the overflow happens before, it doesn't work. But the real problem is the usage of the wrong data type, an INT2 had nothing to do with time. What does 100 mean? 100 seconds? 100 minutes? 100 hours? Nobody knows, a recipe for bugs.

Frank Heikens · Accepted Answer · 2012-08-05 10:27:49Z

4

Why don't you use an INTERVAL for a time interval? It's a perfect solution for your problem.

answered Aug 5, 2012 at 10:27

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

1 Comment

Mike Christensen Over a year ago

That's probably a great solution as well. Making this change now might be somewhat messy though, but I might do some experimenting to see what would be involved. However, Mu's solution works perfectly fine as well and required me to update minimal code.

Collectives™ on Stack Overflow

PostgreSQL - Smallint overflowing when creating index on multiple columns. Is this a bug?

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related