Re: How do I upsert depending on a second table?
От | Juan Rodrigo Alejandro Burgos Mella |
---|---|
Тема | Re: How do I upsert depending on a second table? |
Дата | |
Msg-id | CAHbZ42xv5f-DrCnhrszKzmLrTQzrw1=3OYSyfEzM9rPVXAy_MQ@mail.gmail.com обсуждение исходный текст |
Ответ на | How do I upsert depending on a second table? (Samuel Marks <samuelmarks@gmail.com>) |
Ответы |
Re: How do I upsert depending on a second table?
Re: How do I upsert depending on a second table? |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: