How can I change an existing type from "bigint" to "bigserial"? I have a PostgreSQL table with the following structure:



I simply need to change the TYPE of prove_identity_id from bigint to bigserial.  I read the docs but wasn't able to understand how to legally achieve the change without the following error: PostgreSQL said: type "bigserial" does not exist 

PostgreSQL 10.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit
bigserial is not a type. It's a pseudo-type, a notational convenience that is resolved to type bigint internally, plus a sequence, a column default, a dependency and an ownership.

Basic commands to convert an existing bigint column with existing rows to a bigserial:

CREATE SEQUENCE tbl_tbl_id_seq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE tbl_tbl_id_seq OWNED BY tbl.tbl_id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(tbl_id), 0)) FROM tbl;


setval() to get the SEQUENCE in sync with the currently highest id.  Replace "tbl" with your table name (prove_identity).

However, zooming into your added image (which should be plain text), it looks like it may be a proper bigserial column already. This might all be a misunderstanding, then.

More details:


Creating a PostgreSQL sequence to a field (which is not the ID of the record)


Related:


How to convert primary key from integer to serial?
Safely and cleanly rename tables that use serial primary key columns in Postgres?


In Postgres 10 or later, consider an IDENTITY column as alternative:


Auto increment table column