SELECT 1/COUNT(*) FROM org WHERE name = 'org0' AND owner = 'wrong user';
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 RETURNING id;
COMMIT; ```
On Tue, Sep 23, 2025 at 7:25 PM Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com> wrote: > > 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 > > > El mar, 23 sept 2025 a las 15:37, Samuel Marks (<samuelmarks@gmail.com>) escribió: >> >> 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 >> >>
