Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.

Hi Laurenz, thank for your explanation, I understand now what I was
missing, the process of: ALTER SEQUENCE sequence_name OWNED BY
table_column_name;

now all make sense

Good point

Thanks again

El mié, 18 de oct. de 2023 5:06 p. m., Laurenz Albe <
laurenz.albe@cybertec.at> escribió:

> On Wed, 2023-10-18 at 17:43 +0000, PG Bug reporting form wrote:
> > PostgreSQL version: 15.4
> >
> > example=# CREATE TABLE example (i serial , j text);
> > CREATE TABLE
> > example=# alter table example add column i_new bigint;
> > ALTER TABLE
> > example=# alter table example alter column i drop default ;
> > ALTER TABLE
> > example=# alter table example alter column i_new set default
> > nextval('example_i_seq'::regclass);
> > ALTER TABLE
> > example=# alter table example drop column i;
> > ERROR:  cannot drop column i of table example because other objects
> depend
> > on it
> > DETAIL:  default value for column i_new of table example depends on
> sequence example_i_seq
> > HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>
> That is working as intended.
>
> If you create a "serial" column, PostgreSQL adds a dependency, just like
> this statement would:
>
>   ALTER SEQUENCE example_i_seq OWNED BY example.i;
>
> That dependency makes sure that the sequence is automatically deleted when
> you drop the column.  That relationship is not broken if you change the
> default value or use the sequence elsewhere.
>
> You never created the sequence explicitly, so you should consider it an
> implementation details of "serial", just like the column default.
> Manually changing the default or using the sequence for something else
> messes with that on a lower level.
>
> It is easy to remove the dependency:
>
>   ALTER SEQUENCE example_i_seq OWNED BY NONE;
>
> Consider using the more advanced and standard conforming alternative
> of identity columns.  You will still find ways to mess with the underlying
> sequence, but there is no column default you can change, and the sequence
> name is not visible in the output of "\d", so you are less likely to fall
> into this trap.
>
> Yours,
> Laurenz Albe
>
>