Identity and Sequence

From: Michael Corey <michael(dot)corey(dot)ap(at)nielsen(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Identity and Sequence
Date: 2024-02-16 16:23:44
Message-ID: CAABu8T9rBDauVayWfX9NnK=-nJKPUhX_mNKcEG5gWN3uA9umRg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2024-02-16 16:34:32 Re: Identity and Sequence
Previous Message sud 2024-02-16 06:52:46 Encryption Options