How do I upsert depending on a second table?
От | Samuel Marks |
---|---|
Тема | How do I upsert depending on a second table? |
Дата | |
Msg-id | CAMfPbcbjTE6=yVJjAyiNU=kM24e-0oLo8S1ZZ7dJZy3UKq+b3g@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: How do I upsert depending on a second table?
Re: How do I upsert depending on a second table? Re: How do I upsert depending on a second table? Re: How do I upsert depending on a second table? |
Список | pgsql-general |
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 по дате отправления: