By Dave Chambers


2018-01-02 15:02:21 8 Comments

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 comments

@Erwin Brandstetter 2018-01-02 15:15:05

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:

@Dave Chambers 2018-01-02 15:32:15

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.

@Erwin Brandstetter 2018-01-02 15:39:59

@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.

@Evan Carroll 2018-01-03 01:31:17

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');

Related Questions

Sponsored Content

1 Answered Questions

What can I do to monitor "out of shared memory" issues?

1 Answered Questions

2 Answered Questions

[SOLVED] Unquoting JSON strings; print JSON strings without quotes

1 Answered Questions

3 Answered Questions

2 Answered Questions

[SOLVED] Database "frozen" on ALTER TABLE

1 Answered Questions

[SOLVED] Cannot log in with default user on Mac 10.10

1 Answered Questions

Sponsored Content