Re: Identity and Sequence

On 2/16/24 10:10 AM, David G. Johnston wrote:
> On Fri, Feb 16, 2024 at 10:24 AM Michael Corey
> <michael(dot)corey(dot)ap(at)nielsen(dot)com> wrote:
>
> By explicitly making a column an IDENTITY column it is going to
> make a sequence behind the scenes even if one with a similar name
> exists.  I tried and it created part_tab_part_id_seq1.  Is there
> no way to have it use the original part_tab_part_id_seq?
>
>
> No, the fact that there is even a sequence is mostly an implementation
> detail you shouldn't be concerned with.

Actually you should be concerned with/aware of this detail for the
reason you state below.

>
>  How do I get the data and the sequence in sync?
>
>
> That would be why the alter table command has a bunch of keywords and
> values as part of it.  So you can modify the values to be what you need.

From

https://www.postgresql.org/docs/current/sql-altertable.html

"

|SET /|sequence_option|/|
|RESTART|
<https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-SEQUENCE-OPTION>

These forms alter the sequence that underlies an existing identity
column. /|sequence_option|/ is an option supported by |ALTER
SEQUENCE|
<https://www.postgresql.org/docs/current/sql-altersequence.html>
such as |INCREMENT BY|.

"

You need to know how a sequence works and what can be modified.

>
> David J.
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com