public inbox for [email protected]
help / color / mirror / Atom feedRe: How do I upsert depending on a second table?
2+ messages / 2 participants
[nested] [flat]
* Re: How do I upsert depending on a second table?
@ 2025-09-23 20:57 Adrian Klaver <[email protected]>
2025-09-23 21:02 ` Re: How do I upsert depending on a second table? Samuel Marks <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Adrian Klaver @ 2025-09-23 20:57 UTC (permalink / raw)
To: Samuel Marks <[email protected]>; [email protected]
On 9/23/25 13:36, Samuel Marks wrote:
> 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;
> ```
Also, as shown, there is no conflict so I don't see the condition being
run per:
https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
"
condition
An expression that returns a value of type boolean. Only rows for
which this expression returns true will be updated, although all rows
will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
condition is evaluated last, after a conflict has been identified as a
candidate to update.
"
>
> 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
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How do I upsert depending on a second table?
2025-09-23 20:57 Re: How do I upsert depending on a second table? Adrian Klaver <[email protected]>
@ 2025-09-23 21:02 ` Samuel Marks <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Samuel Marks @ 2025-09-23 21:02 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
Yeah I know my approach doesn't work, my question is, what is the
correct way to do an upsert for this schema?
Specifically:
- Create a new repo if one by that name doesn't exist + requestor is
`owner` of associated `org`
- Update an existing repo if one by that name does exist + requestor
is `owner` of associated `org`
On Tue, Sep 23, 2025 at 3:57 PM Adrian Klaver <[email protected]> wrote:
>
> On 9/23/25 13:36, Samuel Marks wrote:
> > 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;
> > ```
>
> Also, as shown, there is no conflict so I don't see the condition being
> run per:
>
> https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
>
> "
> condition
>
> An expression that returns a value of type boolean. Only rows for
> which this expression returns true will be updated, although all rows
> will be locked when the ON CONFLICT DO UPDATE action is taken. Note that
> condition is evaluated last, after a conflict has been identified as a
> candidate to update.
>
> "
> >
> > 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
> >
> >
>
>
> --
> Adrian Klaver
> [email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-09-23 21:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-23 20:57 Re: How do I upsert depending on a second table? Adrian Klaver <[email protected]>
2025-09-23 21:02 ` Samuel Marks <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox