CODEWITHSUNDEEP
postgresql
trench
trench

Reputation: 5355

Unable to delete from a temp table?

I keep running into an issue where I cannot delete data from a temp table. I can't seem to find relevant hits on Google when I search for the error message though. I tried to set the REPLICA IDENTITY DEFAULT and NOTHING

CREATE TEMP TABLE name (LIKE schema.original);

ERROR: cannot delete from table "name" because it does not have a replica identity and publishes deletes Hint: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

I am trying to follow an ETL example, so my query looks very similar to the one found on this link (the table names and columns are different):

https://github.com/gtoonstra/etl-with-airflow/blob/master/examples/etl-example/sql/process_customer_dimension.sql

My searches keep bring up hits for Logical replication, but when I check my settings I see that it is under replica.

name,setting
wal_level,replica

Upvotes: 8

Views: 25956

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5940

You could try to set identity to complete, with will use the whole row as identifier ALTER TABLE name REPLICA IDENTITY FULL;

or if it has a unique index ALTER TABLE name REPLICA IDENTITY USING INDEX index_name;

It will use the primary key by default, and I suppose there isn't one.

On a side note, why are you publishing a temp table? I think you should remove it, as I see no point in replicating the temp table. Find your pub name SELECT * FROM pg_publication; and delete temp table from it: ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]

Upvotes: 20

Related Questions