public inbox for [email protected]
help / color / mirror / Atom feedFrom: Juan Rodrigo Alejandro Burgos Mella <[email protected]>
To: Samuel Marks <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: How do I upsert depending on a second table?
Date: Wed, 24 Sep 2025 01:44:07 -0500
Message-ID: <CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com> (raw)
In-Reply-To: <CAMfPbcbuww3geDUFce+eQ2DEtg1n=qHQ3N=2LdZYdJ55cUUHBA@mail.gmail.com>
References: <CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com>
<CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1=3OYSyfEzM9rPVXAy_MQ@mail.gmail.com>
<CAMfPbcbuww3geDUFce+eQ2DEtg1n=qHQ3N=2LdZYdJ55cUUHBA@mail.gmail.com>
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
> >>
> >>
>
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: How do I upsert depending on a second table?
In-Reply-To: <CAHbZ42zCptkYZi=QHaeDFHL9adPS1-nE6xoUC5QBa5VcF0dihA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox