Geek asks and answers

PostgreSQL - Smallint overflows when creating an index on multiple columns. This is mistake?

I had a problem adding a value 32767 to a column smallintin Postgres, which would give a smallint error out of range. This was strange because I could:

SELECT 32767::int2;

Which will work fine. After a little hair pull, I finally traced this to the index in the column in question. Firstly, here is the circuit (well, actually, but I simplified this to the case of playback):

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

Now I create the following index:

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

Next, I will try to create the following line:

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

I get an error message:

ERROR: smallint out of range SQL state: 22003

It looks like it idx_test_totaltimeexpects the maximum value int2, even if the index is applied to the sum of two small rows.

Postgres, - ? , int4 CHECK, 32767? Postgres 9.0.0 (, !), SQL Fiddle, 9.1.4.

+5
2

, int2 + int2 - int2, (cooktime + preptime) (32767, 10). :

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

, .

+4

INTERVAL ? .

+3

More articles:


All Articles