PostgreSQL unable to set existing column as IDENTITY

Question

So, I have gone through dozens of helping material, it seems like Postgres changes the way of working quite often. What I am trying to do is, set an existing column as IDENTITY so that it is unique primary key and auto increments on its own. I have found 3 solutions all around the web:

id integer NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
id serial NOT NULL PRIMARY KEY SERIAL
id bigint NOT NULL DEFAULT nextval(some_seq)

The third one seems a hard-coded solution and not very feasible if I have, say, 500 tables, because I will need 500 sequence numbers against each PK, so I can not do that.

The second one is an obsolete method and is not recommended by experts anymore.

This leaves me with the first solution, and for some reason, I can not do it to my 'existing' tables at all. I have literally tried a 100 solutions from the web. Here is one source that I tried from Stackoverflow: How can i change existing column as Identity in PostgreSQL 11.1

And... surprisingly, that same exact approach is not working for me at all. I tried following query:

ALTER TABLE db.table_name ALTER COLUMN id SET NOT NULL, ALTER id ADD GENERATED AS ALWAYS IDENTITY;

And it gives me a very vague error with no information:

ERROR: syntax error at or near ADD

What could be the problem? I can not even add this using GUI either, it gives the same error. My server is running PostgreSQL v9.6.9 on Linux Ubuntu Machine if that helps.

Answer 1

Just to clarify what the documentation says:

Don't use serial

For new applications, identity columns should be used instead.

. . .

When should you?

• If you need support to PostgreSQL older than version 10.
• In certain combinations with table inheritance (but see there)
• More generally, if you somehow use the same sequence from multiple tables, although in those cases an explicit declaration might be preferable over the serial types.

Your use-case falls into the first category. Your version of Postgres is older than 10.0, so the use of serial is fine. One day, when you upgrade Postgres, you can think about changing to generated always as identity.

When it does come time to change them, this blog (which is referenced by the official documentation) has a function to help facilitate the change.

Answer 2

So, I have gone through dozens of helping material,

But not, it would seem the actual official manuals? I don't understand why so many people are happy following advice from random strangers on the internet but ignore the official manuals.

it seems like Postgres changes the way of working quite often.

AFAIK this auto-generated primary-key setup has been changed once. In over twenty years. If that rate of change is too much for you, then I strongly recommend you avoid anything to do with web programming or mobile apps.

What I am trying to do is, set an existing column as IDENTITY so that it is unique primary key and auto increments on its own. I have found 3 solutions all around the web:

id integer NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
id serial NOT NULL PRIMARY KEY SERIAL
id bigint NOT NULL DEFAULT nextval(some_seq)

The third one seems a hard-coded solution and not very feasible if I have, say, 500 tables, because I will need 500 sequence numbers against each PK, so I can not do that.

It's exactly what the second solution does. I don't see why having 500 sequences is something you can't do. Assuming you've managed to get yourself into a position where you have 500 tables you didn't set up correctly in the first place.

My server is running PostgreSQL v9.6.9 on Linux Ubuntu Machine if that helps.

It does indeed.

Let's not go poking around at random nonsense on the internet, let's look at the actual postgresql.org manuals:

https://www.postgresql.org/docs/9.6/sql-createtable.html

No mention of IDENTITY there, is there? No wonder it is complaining about your syntax.

https://www.postgresql.org/docs/12/sql-createtable.html

It is in the current docs, because they added the feature back in version 10.

So - since you seem to have created 500 tables without thinking through your requirements, you are faced with the prospect of doing it yourself.

richardh=> CREATE TABLE t (id integer not null primary key, a text);
CREATE TABLE
richardh=> INSERT INTO t VALUES (1, 'aaa'), (2, 'bbb');
INSERT 0 2
richardh=> CREATE SEQUENCE t_id_seq;
CREATE SEQUENCE
richardh=> ALTER TABLE t ALTER COLUMN id SET DEFAULT nextval('t_id_seq');
ALTER TABLE
richardh=> SELECT setval('t_id_seq', max(id)) FROM t;
 setval 
--------
      2
(1 row)

richardh=> INSERT INTO t VALUES (DEFAULT, 'ccc');
INSERT 0 1
richardh=> SELECT * FROM t ORDER BY id;
 id |  a  
----+-----
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

There - not so terrible, was it?

Obviously you could script this with plpgsql and some EXECUTE commands, or perhaps create an external sql script with some regex grep+transform magic.

Note that the key limitation here is that the sequences are not directly attached to the tables. If you drop a table then the sequence will persist. A key advantage (which may not apply to your use case) is that you can share the sequence numbers between multiple tables (or indeed used directly by an application and not stored in a table at all).

Since you seem averse to change, I should point out that version 9.6 will be end-of-life within a couple of years (probably 2022/2023) and you may wish to consider moving to the current latest release (version 12 as of July 2020) for longer-term stability.