2

I have a PostgreSQL table with the following structure:

enter image description here

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

9

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:

Related:

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

  • Thank you. Your query ran but the type is still showing as bigint but I guess that's ok after reading some of your answers. I'll try to write my data and see if it works. – Dave Chambers Jan 2 '18 at 15:32
  • 3
    @DaveChambers: Some clients (like pgAdmin) display the pseudo-types serial / bigserial, others (like yours? you should declare your client) display the actual data type. It's a purely a matter of representation. – Erwin Brandstetter Jan 2 '18 at 15:39
3

If you're using PostgreSQL 10, you should be using the IDENTITY PROPERTY on the bigint column, and not using the serial or bigserial macros at all - it's Pg specific syntax that is difficult to understand, and in the event of permission modification: easy to mess up.

ALTER TABLE provide_identity
  ALTER COLUMN prove_identity_id
  ADD GENERATED BY DEFAULT
  AS IDENTITY;

But instead of messing with that directly, I'd suggest backing up the database and running this script from the 2nd quadrant blog. Run it like this,

SELECT upgrade_serial_to_identity('public.prove_identity', 'prove_identity_id');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.