I was using the serial datatype to create a PRIMARY KEY column but was recently informed that this is outdated and I should be using GENERATED ALWAYS AS IDENTITY instead. I used pgAdmin 4 to fix it by changing the datatype to integer and then adding the generated as identity. This worked but I am a bit baffled by the result as the SQL code shows 2 id columns instead of 1 now:
CREATE TABLE public.names
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
name character varying(128) COLLATE pg_catalog."default",
CONSTRAINT names_pkey PRIMARY KEY (id)
)
However when I query the table with SELECT * FROM names it still shows me only 1 id column as there was before.
I would appreciate help on understanding what went wrong and how to fix it.
After lengthy search here is what I found:
There is indeed only one column which can be confirmed using psql. However using the command pg_dump -st names <databasename> from bash I noticed that there were 2 sequences being defined associated with the id column: public.names_id_seq and public.names_id_seq1.
This can also be confirmed by running SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'; from psql where both sequences show up.
If one tries to delete public.names_id_seq1 one gets the error ERROR: cannot drop sequence apis_id_seq1 because column id of table apis requires it.
The solution is to delete the first sequence with DROP SEQUENCE names_id_seq; After refreshing pgAdmin4 the second column was gone.
TL;DR
Run SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'; and identify duplicate sequences (ie. the ones where there is a version with and one without a 1 at the end)
Delete the superfluous sequence with DROP SEQUENCE <sequenceName_seq>; (only the one without a 1 at the end can be deleted)
Refresh pgAdmin4