We have two tables, that look something like this:
CREATE TABLE devices(
"id" serial NOT NULL PRIMARY KEY,
"name" varchar(255) NOT NULL,
"last_log_id" integer NULL
);
CREATE TABLE log(
"id" serial NOT NULL PRIMARY KEY,
"created_at" timestamp with time zone NOT NULL,
"msg" varchar(255) NOT NULL,
);
ALTER TABLE "devices" ADD CONSTRAINT
"device_last_log_id" FOREIGN KEY ("last_log_id")
REFERENCES "log" ("id") DEFERRABLE INITIALLY DEFERRED;
What is an efficient query to delete all "log" rows older than a certain "created_at" date, unless they are referenced by the "devices" table "last_log_id" column?
delete from log l
where l.created_at < 'somedate'
and not exists (select 1
from devices d
where d.last_log_id = l.id
);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments