cannot update table Temp Table because it does not have a replica identity and publishes updates in Postgres

Question

I used Postgres Database with replication.
I have used temp table in postgres function. I unable to update Temp Table while updating it through join.

Below is Postgres query(tempallergyupdates is temp table):

 drop table if exists tempallergyupdates;
 create temp table tempallergyupdates(patientallergyid int,updateid int, newupdateid int);
 update tempallergyupdates set patientallergyid = 1;

Above query throws bellow exception:

cannot update table "tempallergyupdates" because it does not have a replica identity and publishes updates

score 23 · Accepted Answer · answered Jul 24 '19 at 20:50

23

We just encountered this and found a solution. It turns out that PostgreSQL does not like tables, even temp tables, that lack a primary key where replication is involved. So either add one to your temp table or use a statement like this after creating the table:

ALTER TABLE table_name REPLICA IDENTITY FULL;

answered Jul 24 '19 at 20:50

ktekinay

426
4
7

sounds logical, as the replication will not be able to identify which row has been updated and therefore replicate this change, so it can only insert new lines and replicate inserts... – Stavros Koureas Nov 18 '22 at 10:55

score 1 · Answer 2 · answered Jan 11 '22 at 17:07

REPLICA IDENTITY FULL works with standard data types in the fields.

ALTER TABLE table_name REPLICA IDENTITY FULL;

but, when a json field appears, you will see a message like:

ERROR: could not identify an equality operator for type json background worker logical replication worker exited with exit code 1

In this case, you must add a new unique index, maybe adding a serial column or simply skipping the json field or adding a new PK, etc.

An tell the replica process use this index.

/* id is a new serial column */
create unique index concurrently idx_someid on tablename (id); 

alter table tablename REPLICA IDENTITY  USING INDEX concurrently;

cannot update table Temp Table because it does not have a replica identity and publishes updates in Postgres

2 Answers2