7

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

2

2 Answers 2

21

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:

Related:

In Postgres 10 or later, consider an IDENTITY column instead. See:

4
  • 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. Commented Jan 2, 2018 at 15:32
  • 4
    @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. Commented Jan 2, 2018 at 15:39
  • 1
    You can't use setval to set the sequence to zero. So where you call setval above, please use coalesce with a default of 1, i.e. SELECT setval('tbl_tbl_id_seq', COALESCE(max(tbl_id), 1)) FROM tbl;
    – balrob
    Commented Feb 26 at 22:42
  • @balrob: Thanks, fixed. Aside: You can set a sequence to 0, if it was created with MINVALUE accordingly. But not with default MINVALUE 1. Commented Feb 26 at 22:52
5

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 and acknowledge you have read our privacy policy.

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