Identity and Sequence
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
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 existsI 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.
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 existsI 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
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.
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|
<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.klaver@aklaver.com