Postgres: integer out of range

Question

I'm running the following query:

WITH match_team_vals(match_id, team_id, is_radiant) AS (
VALUES 
(2281450916, 2783913, true),
(2281450916, 2538753, false)
)

INSERT INTO dota_match_team(match_id, team_id, is_radiant)
SELECT match_id, team_id, is_radiant
FROM match_team_vals
RETURNING id AS lineup_id

on this table:

CREATE TABLE dota_match_team
(
  id serial NOT NULL,
  match_id integer NOT NULL,
  team_id integer,
  is_radiant boolean NOT NULL,
  CONSTRAINT dota_match_teams_pkey PRIMARY KEY (id)
)

The error message I get is

ERROR: integer out of range
SQL state: 22003

I've tried casting the match_id and team_id to bigint. Also looking online I see that people have this issue with the serial hitting the upper limit of integers. This doesn't seem to be the case:

SELECT nextval('dota_match_team_id_seq')
returns 31

23tux · Accepted Answer · 2016-04-09 05:46:39Z

13

Consider altering your table to use a bigger integer (see here for details: http://www.postgresql.org/docs/9.1/static/datatype-numeric.html).

I think the problem is, that your match_id and team_id are of type integer and you try to insert the value 2281450916, but integer's maximum is 2147483647

answered Apr 9, 2016 at 5:46

23tux

14.8k16 gold badges98 silver badges195 bronze badges

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

1 Comment

janderson Over a year ago

Whoops. I changed all of my other ints to bigints but missed these! Thanks a lot.

Shubham Batra · Accepted Answer · 2016-04-09 07:52:30Z

2

You can run this query:

ALTER TABLE dota_match_team alter column match_id type bigint;

this type cast solve the error for match_id. if you thinkIt is error of serial limit then you can also do.

  SELECT setval('dota_match_team_id_seq' , 100000000);

answered Apr 9, 2016 at 7:52

Shubham Batra

2,3755 gold badges32 silver badges48 bronze badges

Collectives™ on Stack Overflow

Postgres: integer out of range

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related