By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table image(
image_uuid uuid primary key default gen_random_uuid()
,image_series_uuid uuid);
CREATE TABLE
--this won't work even on an empty table
INSERT INTO image ( image_uuid, image_series_uuid )
SELECT*FROM
(VALUES('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'85c4a85a-10de-47d3-8af5-1c090e119a86'::uuid)
,('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'8a10fa91-faab-406c-bc79-4dcc03ad3d75'::uuid))_
WHERE NOT EXISTS (
SELECT FROM image WHERE image_uuid = 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f'
);
ERROR: duplicate key value violates unique constraint "image_pkey" DETAIL: Key (image_uuid)=(d9aaf41a-9c88-488b-b371-0c0bb1165a4f) already exists.
--this uses distinct on to discard incoming duplicates
INSERT INTO image ( image_uuid, image_series_uuid )
SELECT DISTINCT ON(1)*FROM
(VALUES('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'85c4a85a-10de-47d3-8af5-1c090e119a86'::uuid)
,('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'8a10fa91-faab-406c-bc79-4dcc03ad3d75'::uuid))_
WHERE NOT EXISTS (
SELECT FROM image WHERE image_uuid = 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f')
ORDER BY 1
RETURNING*;
| image_uuid | image_series_uuid |
|---|---|
| d9aaf41a-9c88-488b-b371-0c0bb1165a4f | 85c4a85a-10de-47d3-8af5-1c090e119a86 |
INSERT 0 1
INSERT INTO image ( image_uuid, image_series_uuid )
VALUES('d9aaf41a-9c88-488b-b371-0c0bb1165a4f','85c4a85a-10de-47d3-8af5-1c090e119a86')
ON CONFLICT DO NOTHING
RETURNING *;
| image_uuid | image_series_uuid |
|---|
INSERT 0 0