0

I'm new to PostgreSQL and I need to convert an existing non-partitioned table to a partitioned table. Since you can't alter an existing table to be partitioned, I created a new partitioned table. According to the PostgreSQL documentation, you can't add constraints to a partitioned table. Constraints must be added to partitions. When I try to add Primary Key constraints to the partitions, I get an error. Can you suggest a way to add constraints to this partition?

purchase_history_within_10_months is the new partition name.

SQL query:

ALTER TABLE ONLY purchase_history_within_10_months ADD CONSTRAINT purchase_history_pkey PRIMARY KEY (transactionid);

Error Statement:

ERROR: relation "purchase_history_pkey" already exists

SQL state: 42P07

According to the error message, there is an existing constraint with the same name for this partition.

I have checked the existing constraints for this partition.

SQL query:

appxxxx=# SELECT constraint_name, constraint_type, table_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name = 'purchase_history_within_10_months'; constraint_name | constraint_type | table_name
------------------------+-----------------+----------------------------------- 2200_30127_1_not_null | CHECK | purchase_history_within_10_months 2200_30127_14_not_null | CHECK | purchase_history_within_10_months 2200_30127_16_not_null | CHECK | purchase_history_within_10_months 2200_30127_36_not_null | CHECK | purchase_history_within_10_months (4 rows)

appxxxx=# appxxxx=#

According to this, there is no constraint with the same name on this partition. So, what is causing this issue?

SebDieBln
  • 3,303
  • 1
  • 7
  • 21
Dhanushka Ekanayake
  • 1
  • 3
  • See also [At what level do Postgres index names need to be unique?](https://stackoverflow.com/questions/27306539/at-what-level-do-postgres-index-names-need-to-be-unique) – SebDieBln Aug 22 '23 at 08:07

1 Answers1

2

According to this, there is no constraint with the same name on this partition. So, what is causing this issue?

In Postgres constraint names need to be unique within the schema, not just within the table. So somewhere in you schema there is already another constraint by that name.

I recommend following the automatic nameing scheme and prefixing each constraint name by the table name, so in your case the constraint would be named purchase_history_within_10_months_pkey.

SebDieBln
  • 3,303
  • 1
  • 7
  • 21
  • Yes, you are correct. Although the new table does not have a constraint with the same name, the old table does. I cannot change the name of this constraint. Therefore, I have to use the same name. The old table can be deleted after the data is copied to the new table. If we try this method, we will first copy the data from the old table, delete the old table, and then alter the new table and add the constraint with the same name. My question is, if we do this, will the constraint apply to the data that we already copied? Because we copied the data before adding the constraint. – Dhanushka Ekanayake Aug 22 '23 at 08:29
  • 1
    Yes, a newly added constraint generally also applies to already existing data. I would however recommend creating the constraint with a temporary name before inserting the data and renaming it afterwards to the desired name. See [Constraint name update in PostgreSQL](https://stackoverflow.com/questions/971786/constraint-name-update-in-postgresql) – SebDieBln Aug 22 '23 at 09:18
  • This way, it works. I alter existing constraint name and create new constraint before copying data to table. – Dhanushka Ekanayake Aug 22 '23 at 10:32