Problem statement : in a postgresql table I am getting an error as "Integer out of range" for the column 'id' type 'serial integer'

RCA : As it is a serial integer column which already exceeded its maximum range of 2147483647. And it is unable to load more data

Solution : Need to change the datatype of the column from INTEGER to BIGINT.

for this solution I tried to execute the ALTER command ALTER TABLE table_name ALTER COLUMN id TYPE BIGINT But the table consists of more than 2 billion data, it is taking time also ALTER command creating some duplicate tables internally which is consuming all the space in the DB.

As a alternative method we are trying different workaround that is to create a copy of the table with id column as BIGINT instead of INTEGER. Then move the data from old table to new table

QUESTION: How to create a new copy table with same Table definition/structure, indexes and constraints but without data? OR any other possible ways to solve my problem of 'Integer_out_of_range'

Thanks in Advance

CodePudding user response：

You can use LIKE and INCLUDING:

CREATE TABLE new_table(LIKE current_table INCLUDING ALL);