Sequence must have same owner as table it is linked to

Question

have following sql, got error in last line, ERROR: sequence must have same owner as table it is linked to [Failed SQL: (0) ALTER SEQUENCE test_table_seq OWNED BY test_table.id

CREATE SEQUENCE test_table_seq;

ALTER TABLE test_table ALTER COLUMN id SET DEFAULT nextval('test_table_seq');
ALTER TABLE test_table ALTER COLUMN id SET NOT NULL;
ALTER SEQUENCE test_table_seq OWNED BY test_table.id;

Any suggestions how to avoid that error? thanks!!!!

Answer 1

Citation:

OWNED BY table_name.column_name

The OWNED BY option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. If specified, this association replaces any previously specified association for the sequence. The specified table must have the same owner and be in the same schema as the sequence. Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

So, the first step: get to know the source table owner. As always there are several ways. The most common:

1. Using psql:

\dt test_table;
           List of relations
┌────────┬────────────┬───────┬─────────┐
│ Schema │    Name    │ Type  │  Owner  │
├────────┼────────────┼───────┼─────────┤
│ public │ test_table │ table │ <name>  │
└────────┴────────────┴───────┴─────────┘

where the <name> value is your target

2. Using a query:

select
  relnamespace::regnamespace as "schema",
  relowner::regrole
from pg_class where relname = 'test_table';

┌────────┬──────────┐
│ schema │ relowner │
├────────┼──────────┤
│ public │  <name>  │
└────────┴──────────┘

Again, <name> is your target.

Note that the schema is not specified here as a condition. If your table is in the schema different then public then it could be more complicated a bit.

Finally, having the sequence already exists:

alter sequence test_table_seq owner to <name>;

where the <name> is the value from one of the previous step.

For now your sequence satisfies the condition mentioned above and you able to associate it with the table.column