Thread: Identity and Sequence

Identity and Sequence

From
Michael Corey
Date:
What is the proper way to deal with identity columns on a table rebuild/partitioning?  If I have the following non-partitioned table

CREATE TABLE IF NOT EXISTS part_tab
(
    part_id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 0 MINVALUE 0 MAXVALUE 9223372036854775807 CACHE 1 ),
    part_name character varying(15) COLLATE pg_catalog."default" NOT NULL,
    recv_day character varying(8) COLLATE pg_catalog."default" NOT NULL
)

In the database, it appears that there is a sequence called part_tab_part_id_seq created for the IDENTITY column.

              Sequence "part_tab_part_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     0 |       0 | 9223372036854775807 |         1 | no      |     1


Over time the table has N number of rows, so I want to partition the table. My steps are:

1. Rename current nonpartitioned table
2. Create a new partitioned table
3. Insert data from the nonpartitioned table to the partitioned table

My partitioned table looks like this from pg_dump ( I added the partition by clause )

CREATE TABLE IF NOT EXISTS part_tab (
    part_id bigint NOT NULL,
    part_name character varying(15) NOT NULL,
    recv_day character varying(8) NOT NULL
) PARTITION BY RANGE (recv_day) ;

pg_dump then adds this about the IDENTITY column and sequence

ALTER TABLE part_tab ALTER COLUMN part_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME part_tab_part_id_seq
    START WITH 0
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
    CACHE 1
);

If I run these two statements I get an error
ERROR:  relation "part_tab_part_id_seq" already exists

I tried different combinations of this ALTER TABLE statement and none seem to work.  What is the proper way to reattach the identity and the sequence bearing in mind that I will load the new table with the data from the old table?

--
Michael Corey

Re: Identity and Sequence

From
"David G. Johnston"
Date:
On Fri, Feb 16, 2024 at 9:24 AM Michael Corey <michael.corey.ap@nielsen.com> wrote:
If I run these two statements I get an error
ERROR:  relation "part_tab_part_id_seq" already exists

I tried different combinations of this ALTER TABLE statement and none seem to work.  What is the proper way to reattach the identity and the sequence bearing in mind that I will load the new table with the data from the old table?


So remove the generated by default from the old non-partitioned table (manually drop the sequence if needed too'.  Or just choose a different sequence name for the new one.

"reattach the identity" isn't a thing - the system is telling you it is creating a new one and you will need to synchronize it to your data.

David J.

Re: Identity and Sequence

From
Michael Corey
Date:
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?  How do I get the data and the sequence in sync?  


On Fri, Feb 16, 2024 at 11:35 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Feb 16, 2024 at 9:24 AM Michael Corey <michael.corey.ap@nielsen.com> wrote:
If I run these two statements I get an error
ERROR:  relation "part_tab_part_id_seq" already exists

I tried different combinations of this ALTER TABLE statement and none seem to work.  What is the proper way to reattach the identity and the sequence bearing in mind that I will load the new table with the data from the old table?


So remove the generated by default from the old non-partitioned table (manually drop the sequence if needed too'.  Or just choose a different sequence name for the new one.

"reattach the identity" isn't a thing - the system is telling you it is creating a new one and you will need to synchronize it to your data.

David J.



--
Michael Corey

Re: Identity and Sequence

From
"David G. Johnston"
Date:
On Fri, Feb 16, 2024 at 10:24 AM Michael Corey <michael.corey.ap@nielsen.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.
 
 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.

David J.

Re: Identity and Sequence

From
Adrian Klaver
Date:


On 2/16/24 10:10 AM, David G. Johnston wrote:
On Fri, Feb 16, 2024 at 10:24 AM Michael Corey <michael.corey.ap@nielsen.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

These forms alter the sequence that underlies an existing identity column. sequence_option is an option supported by ALTER SEQUENCE such as INCREMENT BY.

"

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


David J.

-- 
Adrian Klaver
adrian.klaver@aklaver.com