Let's just say there is a table called "plays" with the following columns:
play_id: primary key, varchar, uuid
event_id: foreign key, varchar
player_id: foreign key, varchar,
date: Date object
time: timestamp object
The values of event_id and player_id might not have existed in the targeted tables (which are events and players respectively) when inserting new rows into this table, and it's possible to insert rows with the same play_id multiple times.
What I tried is
INSERT INTO plays (play_id, event_id, player_id, date, time)
VALUES ('e092068d-edc5-4e67-99ea-b2b429eaa4f0', '12345', '54321', '2023-01-01', '08:23:34.23'),
'027d427b-ca50-4756-8513-185f1bf69665', '418590', '16263', '2023-03-12', '12:04:53.00'),
('03c67512-5529-4142-b443-f7994ed7b2bc', '418590', '16263', '2023-03-16', '10:31:31.70'),
('06885f46-b3e7-43ce-be00-a08fc9e220b6', '443484', '17057', '2023-03-28', '17:17:18.63')
ON CONFLICT (play_id) DO UPDATE SET play_od = EXCLUDED.play_id, event_id = EXCLUDED.event_id, player_id = EXCLUDED.player_id, date = EXCLUDED.date, time = EXCLUDED.time
ON CONFLICT ON CONSTRAINT plays_event_id_fkey DO NOTHING
ON CONFLICT ON CONSTRAINT plays_player_id_fkey DO NOTHING;
However, it says that there is a syntax error at or near the second "ON", which kind of indicates that we are not allowed to use multiple ON CONFLICT in one command at the same time. If so, is there a way to work around and how? Do I need to create a custom constraint for this scenario?
