Hi Samuel
Using ON CONFLICT is a headache.
It's better to use the versatility of a Trigger: you have the full record at your fingertips, and if you're going to UPDATE, you have the previous record too.
There's much more control.
Also, you can always count on the beloved foreign keys, which are also quite useful.
Atte.
JRBM
Attempt:
```sql
CREATE TABLE org
(
"name" VARCHAR(50) PRIMARY KEY,
owner VARCHAR(50) NOT NULL
);
CREATE TABLE repo
(
"id" INTEGER PRIMARY KEY,
full_name VARCHAR(255) UNIQUE NOT NULL,
org VARCHAR(50) NOT NULL REFERENCES org ("name")
);
INSERT INTO org(name, owner) VALUES ('org0', 'user0');
INSERT INTO repo (id, full_name, org)
VALUES (0, 'org0/name0 by wrong user', 'org0')
ON CONFLICT (full_name) DO UPDATE
SET full_name = EXCLUDED.full_name,
org = EXCLUDED.org
WHERE EXISTS (SELECT 1
FROM org org_tbl
WHERE org_tbl.name = EXCLUDED.org
AND org_tbl.owner = 'wrong user')
RETURNING *;
SELECT * FROM repo WHERE id = 0;
```
This all succeeds. It should fail because the 'wrong user' is trying
to create a new—or update an existing—repo.
Thanks for all suggestions
