PostgreSQL primary key id datatype from serial to bigserial?

Question

I did some research but can't find the exact answer that I look for. Currently I have a primary key column 'id' which is set to serial but I want to change it to bigserial to map to Long in Java layer. What is the best way to achieve this considering this is a existing table? I think my Postgres version is 10.5. Also I am aware that both serial and bigserial are not a data type.

Answer 1

In Postgres 9.6 or earlier the sequence created by a serial column already returns bigint. You can check this using psql:

drop table if exists my_table;
create table my_table(id serial primary key, str text);

\d my_table

                            Table "public.my_table"
 Column |  Type   | Collation | Nullable |               Default                
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('my_table_id_seq'::regclass)
 str    | text    |           |          | 
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id)


\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
Owned by: public.my_table.id

So you should only alter the type of the serial column:

alter table my_table alter id type bigint;

---

The behavior has changed in Postgres 10:

Also, sequences created for SERIAL columns now generate positive 32-bit wide values, whereas previous versions generated 64-bit wide values. This has no visible effect if the values are only stored in a column.

Hence in Postgres 10+:

alter sequence my_table_id_seq as bigint;
alter table my_table alter id type bigint;

Answer 2

-- backup table first

CREATE TABLE tablenamebackup as select * from tablename ;

--add new column idx

alter table tablename add column idx bigserial not null;

-- copy id to idx 

update tablename set idx = id ;

-- drop id column

alter table tablename drop column id ;

-- rename idx to id 

alter table tablename rename column idx to id ;

-- Reset Sequence to max + 1

SELECT setval(pg_get_serial_sequence('tablename', 'id'), coalesce(max(id)+1, 1), false) FROM tablename ;