In my Postgres 13 database I have a master table suggestions
and multiple child tables with different sets of columns, two of them are movie_edit_suggestions
and series_edit_suggestions
.
The id of the child tables are foreign keys to the id of the master table and ids are unique across all child tables, i.e. id “1” can’t exist in both movie_edit_suggestions
and release_edit_suggestions
.
In these child tables there are a pair of columns that must be unique within each table:
author integer NOT NULL,
field text NOT NULL
These tables also has the value text NOT NULL
column. If the same author tries to do two inserts with the same field then the latest value
should update the existing one.
CREATE TYPE suggestion_type AS ENUM (
'change_movie',
'change_series'
);
CREATE TABLE suggestions (
id SERIAL PRIMARY KEY,
suggestion_type suggestion_type NOT NULL,
author integer NOT NULL
);
ALTER TABLE ONLY suggestions
ADD CONSTRAINT suggestions_id_suggestion_type_author_key UNIQUE (id, suggestion_type, author);
CREATE TABLE movie_edit_suggestions (
id SERIAL PRIMARY KEY,
author integer NOT NULL,
field text NOT NULL,
value text NOT NULL,
suggestion_type suggestion_type DEFAULT 'change_movie'::suggestion_type,
CONSTRAINT movie_edit_suggestions_suggestion_type_check CHECK ((suggestion_type = 'change_movie'::suggestion_type))
);
CREATE UNIQUE INDEX unique_movie_edit_suggestions ON movie_edit_suggestions USING btree (field, author);
ALTER TABLE ONLY movie_edit_suggestions
ADD CONSTRAINT movie_edit_suggestions_id_suggestion_type_author_fkey FOREIGN KEY (id, suggestion_type, author) REFERENCES suggestions(id, suggestion_type, author) ON DELETE CASCADE;
CREATE TABLE series_edit_suggestions (
id SERIAL PRIMARY KEY,
author integer NOT NULL,
field text NOT NULL,
value text NOT NULL,
suggestion_type suggestion_type DEFAULT 'change_series'::suggestion_type,
CONSTRAINT series_edit_suggestions_suggestion_type_check CHECK ((suggestion_type = 'change_series'::suggestion_type))
);
CREATE UNIQUE INDEX unique_series_edit_suggestions ON series_edit_suggestions USING btree (field, author);
ALTER TABLE ONLY series_edit_suggestions
ADD CONSTRAINT series_edit_suggestions_id_suggestion_type_author_fkey FOREIGN KEY (id, suggestion_type, author) REFERENCES suggestions(id, suggestion_type, author) ON DELETE CASCADE;
I’m trying to do this with CTE’s but by first attempt results in duplicate suggestions
entries:
WITH suggestion AS (
INSERT INTO suggestions (suggestion_type, author) VALUES ('change_movie', 1)
RETURNING id
)
INSERT INTO movie_edit_suggestions
(id, suggestion_type, field, value, author)
VALUES
((SELECT id FROM suggestion), 'change_movie', 'title', 'Star Wars', 1)
ON CONFLICT (field, author) DO UPDATE SET value = 'Star Wars';
-- Another one
WITH suggestion AS (
INSERT INTO suggestions (suggestion_type, author) VALUES ('change_movie', 1)
RETURNING id
)
INSERT INTO movie_edit_suggestions
(id, suggestion_type, field, value, author)
VALUES
((SELECT id FROM suggestion), 'change_movie', 'title', 'Star Wars: New Hope', 1)
ON CONFLICT (field, author) DO UPDATE SET value = 'Star Wars: New Hope'
SELECT * FROM movie_edit_suggestions
id |
author |
field |
value |
suggestion_type |
1 |
1 |
title |
Star Wars: New Hope |
change_movie |
SELECT * FROM suggestions
id |
suggestion_type |
author |
1 |
change_movie |
1 |
2 |
change_movie |
1 |
Swapping the inserts results in an error when inserting to both child tables:
WITH movie_edit_suggestion AS (
INSERT INTO movie_edit_suggestions
(suggestion_type, field, value, author)
VALUES
('change_movie', 'title', 'Star Wars', 1)
ON CONFLICT (field, author) DO UPDATE SET value = 'Star Wars'
RETURNING
id, field, value
)
INSERT INTO suggestions (id, suggestion_type, author)
VALUES ((SELECT id FROM movie_edit_suggestion), 'change_movie', 1)
-- Another one
WITH series_edit_suggestion AS (
INSERT INTO series_edit_suggestions
(suggestion_type, field, value, author)
VALUES
('change_series', 'title', 'Breaking Bad', 1)
ON CONFLICT (field, author) DO UPDATE SET value = 'Breaking Bad'
RETURNING
id, field, value
)
INSERT INTO suggestions (id, suggestion_type, author)
VALUES ((SELECT id FROM series_edit_suggestion), 'change_series', 1)
ERROR: duplicate key value violates unique constraint “suggestions_pkey”
DETAIL: Key (id)=(1) already exists.
Full dbfiddle showcasing both attempts