The insert works because there is no data in the repo table that conflicts with the entered full name.
JRBm El mar, 23 sept 2025, 23:19, Samuel Marks <[email protected]> escribió: > Ok so you're thinking I give up on putting it all in one query and > instead use a transaction? - Is that the recommended way? > > ```sql > TRUNCATE repo, org; > INSERT INTO org(name, owner) VALUES ('org0', 'user0'); > ``` > > ```sql > START TRANSACTION READ WRITE; > > 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 > <[email protected]> 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 (<[email protected]>) > 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 > >> > >> >
