How to change primary key type in postgreSQL to bigserial using Rails/ActiveRecord

Question

How can I change the type of the primary key column of an existing table from serial (int) to bigserial (bigint) in postgres?

The database is used by a Rails App. Do I need to make any changes to the app?

Answer 1

I believe the accepted answer is only half the story. You must also modify your sequence.

In particular, after only change the type of the id column, you still have:

# \d my_table_id_seq
                   Sequence "public.my_table_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1

You then need to run alter sequence mytable_id_seq as bigint;

The result is:

# \d my_table_id_seq
                       Sequence "public.my_table_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1