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?
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
