error to create sequence

Question

trying to add auto_increment to an existing column

ALTER TABLE table_name ALTER COLUMN id_column SET DEFAULT nextval('table_id_column_seq');

try 1:

CREATE SEQUENCE table_id_column_seq AS integer START 1 OWNED BY table.id_column;

Error:

ERROR: sequence must have same owner as table it is linked to

try 2:

CREATE SEQUENCE table_id_column_seq AS integer START 1 OWNED TO postgres;

Error:

ERROR: syntax error at or near "integer"
LINE 1: CREATE SEQUENCE table_id_column_seq integer START 1...
 ^

As it should be?

• user1822: identity columns are the preferred way to create an "auto increment" column these days
• Richard: @a_horse_with_no_name what would that be like?

Answer 1

With modern Postgres versions (i.e. >= 10) it's better to use identity columns (they do use sequences in the background).

To turn an existing column into an identity column you can use:

ALTER TABLE table_name 
 ALTER COLUMN id_column 
 ADD GENERATED ALWAYS AS IDENTITY;

or

ALTER TABLE table_name 
 ALTER COLUMN id_column 
 ADD GENERATED BY DEFAULT AS IDENTITY;

I prefer the generated always as it will through an error if you try to bypass the automatic generation of values.

If the table already contains data, you need to synchronized the underlying sequence with the values in the table:

select setval(pg_get_serial_sequence('the_table', 'id_column'), max(id))
from the_table;

Answer 2

As mentioned in the documentation, the OWNED BY refers to table and column this sequence belongs to, not the owning user.

I suggest you perform the CREATE SEQUENCE as the user owning the table, or if this should not be possible, to change the owner to yourself, create the sequence, then change the owner of table and sequence back to the original one.