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 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), 1)) FROM tbl;

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

However, it looks like you have a proper bigserial column already. This might all be a misunderstanding, then.

See:

• 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 instead. See:

• Auto increment table column

StackExchange Database Administrators Q#194383, answer score: 21