ActiveRecord: Error inserting bigint into PostgreSQL DB

[Michael Dippery]

Hi everyone,

I have another quick question for the group. I’m working on a Rails app backed by a PostgreSQL 8.1 database. I’m trying to insert large numbers into the database. Specifically, I’m trying to insert the value 536,870,912,000, which is, of course, greater than the max value for a 32-bit integer. I believe Postgres integer columns are 4 bytes, so I changed the column type to be a bigint, which should allow 8-byte values. 536,870,912,000 is less than the max value for a 64-bit integer, but when trying to insert into the database, I get this error: “PGError: ERROR:  integer out of range”. Examining the database via Postgres’s console reports that the column is, in fact, a bigint:

hard_drive          | bigint                 |

Any ideas what may be going wrong?

Thanks,

— Michael

[Armando Di Cianno]

Hrm, works directly from psql here:

foo=> CREATE TABLE fum ( num bigint );
CREATE TABLE
foo=> INSERT INTO fum (num) VALUES (536870912000);
INSERT 0 1
foo=> SELECT * FROM fum;
num
--------------
536870912000
(1 row)

Are you trying this from the Rails app or from script/console? If so,
have you not restarted the app, or console, and does the behavior
change if you do? That'd be handy to know.

__armando

> --
>
> You received this message because you are subscribed to the Google Groups
> "TechValley Ruby Brigade" group.
> To post to this group, send email to techva...@googlegroups.com.
> To unsubscribe from this group, send email to
> techvalleyrb...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/techvalleyrb?hl=en.
>

[Michael Dippery]

> Hrm, works directly from psql here:
>
> foo=> CREATE TABLE fum ( num bigint );
> CREATE TABLE
> foo=> INSERT INTO fum (num) VALUES (536870912000);
> INSERT 0 1
> foo=> SELECT * FROM fum;
> num
> --------------
> 536870912000
> (1 row)
>
> Are you trying this from the Rails app or from script/console? If so,
> have you not restarted the app, or console, and does the behavior
> change if you do? That'd be handy to know.

Well, as it turns out, the problem is that I’m an idiot. I had another column that also stored large integers, but I _thought_ the integers being inserted were small enough to fit into a 32-bit integer datatype. That was partly true — the integer was small enough to fit into a 32-bit _unsigned_ integer — but, of course, a PostgreSQL integer is a _signed_ datatype, which for some reason I forgot. Once I fixed that issue, the problem went away.


Thanks,
— Michael