5

my question is related to this question. I want to switch two id of a table which are defined as primary keys.

But unfortunately the table where I want to switch the ids was already created and the primary key constraint was not set do deferrable.

I tried to set it to deferrable with the following statement: alter table table_name alter constraint primary_key_name_pkey deferrable;

And got an error: constraint "primary_key_name_pkey" of relation "table_name" is not a foreign key constraint

Is there any way to set a primary key constraint to deferrable after creation?

Thanks for your help.

Community
  • 1
  • 1
János
  • 153
  • 6
  • I think you need to drop and re-create the PK mark it as deferrables when you create it. –  Mar 30 '17 at 15:48
  • For a single pair of rows, a possible workaround *could* be to swap via a temp value. (this could affect triggers, etc, but changing a PK value is suspect anyway ...) – wildplasser Mar 30 '17 at 18:57
  • It seems that you cant defer a PK if there are foreign key contrsints referencing it. My workarround was a small python script that dropped all foreign key constraints and the PK itself, did the id swaps of the ids and then restored all constraints again. – János Apr 03 '17 at 08:06

1 Answers1

1

As written in the source code of PostgreSQL:

/*
 * ALTER TABLE ALTER CONSTRAINT
 *
 * Update the attributes of a constraint.
 *
 * Currently only works for Foreign Key constraints.
 * Foreign keys do not inherit, so we purposely ignore the
 * recursion bit here, but we keep the API the same for when
 * other constraint types are supported.

Source: https://github.com/postgres/postgres/blob/master/src/backend/commands/tablecmds.c#L9557

So, it's not possible, even with Pg12.

Thomas B
  • 181
  • 1
  • 13