[FIXED] how change data type already registered in the database

Issue

I'm working now on a system that defines a PK (primary key) of the table as int, this PK has a sequence with a rule:

"YearYearMonthMonth" + 6 numbers.

ex: 2105000001

But the year of "22" (2022) was not predicted, or, if I were to adjust this pattern of the sequence to the current year, it would give an error, as it exceeds the max value. of an int.

Doubt: the system is huge, in java, PostgreSQL database, what would be the pros and cons to switch to Long? Would it bring database performance impacts something like that? Would all IDs already saved in the bank as int automatically change to Long? Sorry, but I don't know the safest way to solve this.. thank you

Solution

You can change the data type without losing any values using ALTER TABLE:

 alter table the_table
     alter the_column type bigint;

Note that this will lock the table and its content will be re-rewritten. So you will have to stop any application accessing that table.

You also need to alter the types of all columns referencing that table.

Answered By - a_horse_with_no_name

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)