When adding an ID, Sequence must have same owner as table it is linked to

Question

I'm trying to add an ID to the table

ALTER TABLE public.priz02
  ADD COLUMN id SERIAL NOT NULL PRIMARY KEY;

but I get an error

sequence must have same owner as table it is linked to

Current user is postgres;

Answer 1

To fix this error, you should make sure that table and sequence owner is same before adding id column. Try this updated code;

-- First check the current ownership of table and sequence
SELECT table_name, table_schema, table_owner
FROM information_schema.tables
WHERE table_name = 'priz02' AND table_schema = 'public';

SELECT column_name, column_default
FROM information_schema.columns
WHERE table_name = 'priz02' AND column_name = 'id';

-- Changing the sequence ownership to match the table's owner
ALTER SEQUENCE public.priz02_id_seq OWNED BY public.priz02.id;

-- Adding the 'id' column
ALTER TABLE public.priz02
ADD COLUMN id SERIAL NOT NULL PRIMARY KEY;

Hope it works :)