6Tjhwv4T

1--- Setup
2
3CREATE SCHEMA IF NOT EXISTS advisory;
4DROP TABLE IF EXISTS advisory.authors;
5DROP TABLE IF EXISTS advisory.articles;
6
7CREATE TABLE advisory.articles (
8    articleid bigserial PRIMARY KEY,
9    text text NOT NULL
10);
11
12CREATE TABLE advisory.authors (
13    id bigserial PRIMARY KEY,
14    articleid bigint NOT NULL REFERENCES advisory.articles (articleid),
15    name text NOT NULL
16);
17
18INSERT INTO advisory.articles (text)
19        VALUES ('article 1'),('article 2');
20TRUNCATE advisory.authors;
21INSERT INTO advisory.authors (articleid, name)
22        VALUES (1,'a'),(1,'b'),(1,'c'),(2,'alone');
23
24--- Final query
25
26BEGIN TRANSACTION;
27   SELECT pg_advisory_xact_lock(2); --article id
28
29   WITH authors_count_per_article AS 
30   (
31     SELECT COUNT(*)
32     FROM advisory.authors
33     WHERE articleid = 2
34   )
35   DELETE 
36   FROM advisory.authors
37    WHERE EXISTS (SELECT 1 FROM authors_count_per_article WHERE count > 1)
38      AND advisory.authors.name = 'alone'; -- will return 0 because only one author
39COMMIT;