Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE message (
  id uuid
, collection_id uuid
, type text
, affiliation_id integer
, status text
, scheduled_for_date date
);

INSERT INTO message (id, collection_id, type, affiliation_id, status, scheduled_for_date)
VALUES
  ('1143c066-01ed-4eb5-a146-68487de702a9', 'bce85e31-4d2f-43b1-b263-57fca356856f', 'multi-store'   , 12091, 'draft', null)
, ('e1183732-e91d-42eb-9998-110e039cfc25', 'bce85e31-4d2f-43b1-b263-57fca356856f', 'multi-store'   , 12092, 'draft', null)
, ('8f962a49-7da1-46d0-87b9-595788767dfe', 'bce85e31-4d2f-43b1-b263-57fca356856f', 'multi-store'   , 12097, 'draft', null)
, ('6e4dee09-7a4e-47be-bdd8-935a67bb2063', '740f6b42-bbf1-4aeb-8fe9-6874635d9e29', 'multi-store'   , 12091, 'draft', null)
, ('79afab0e-14e7-4d1b-9a15-358763743c3e', '740f6b42-bbf1-4aeb-8fe9-6874635d9e29', 'multi-store'   , 12092, 'draft', null)
, ('7bc78bee-074a-4031-9492-954e7c4eeb09', '740f6b42-bbf1-4aeb-8fe9-6874635d9e29', 'multi-store'   , 12097, 'draft', null)
, ('3bb38fbd-d411-4f78-9c42-c858bf57b784', null                                  , 'standard-store', 10511, 'draft', null)
, ('fbb3b175-1a3b-4515-b0b3-0ce6d6d0145f', null                                  , 'standard-store', 10511, 'draft', null)
, ('84004999-d2cf-4af4-bfaa-c1077d1d8621', null                                  , 'standard-store', 10511, 'sent', '2017-05-21')
, ('cbea0789-6886-431a-a8e1-723d5aafc7b9', null                                  , 'standard-store', 10511, 'scheduled', '2019-02-12')
, ('ec8988ff-5136-4b81-b448-cd456dc487a4', null                                  , 'standard-store', 10511, 'review', '2019-01-13')
, ('0e119440-5fbc-4afe-a784-a6bcfe3a6e4d', null                                  , 'standard-store', 10511, 'draft', null)
, ('98503a20-4396-4809-b3ec-8e330c15afa9', null                                  , 'standard-store', 10511, 'needs_action', '2018-12-11')
, ('d33a9173-dc64-464f-8e58-49b4c9c2fdae', null                                  , 'standard-store', 10511, 'draft', null)
, ('bee0dc72-acca-44e2-82ea-d18e830f91a2', null                                  , 'standard-store', 10511, 'sent', '2016-03-12')
  RETURNING *;

CREATE INDEX ON message (collection_id, affiliation_id) WHERE type = 'multi-store';

CREATE TABLE

id	collection_id	type	affiliation_id	status	scheduled_for_date
1143c066-01ed-4eb5-a146-68487de702a9	bce85e31-4d2f-43b1-b263-57fca356856f	multi-store	12091	draft	null
e1183732-e91d-42eb-9998-110e039cfc25	bce85e31-4d2f-43b1-b263-57fca356856f	multi-store	12092	draft	null
8f962a49-7da1-46d0-87b9-595788767dfe	bce85e31-4d2f-43b1-b263-57fca356856f	multi-store	12097	draft	null
6e4dee09-7a4e-47be-bdd8-935a67bb2063	740f6b42-bbf1-4aeb-8fe9-6874635d9e29	multi-store	12091	draft	null
79afab0e-14e7-4d1b-9a15-358763743c3e	740f6b42-bbf1-4aeb-8fe9-6874635d9e29	multi-store	12092	draft	null
7bc78bee-074a-4031-9492-954e7c4eeb09	740f6b42-bbf1-4aeb-8fe9-6874635d9e29	multi-store	12097	draft	null
3bb38fbd-d411-4f78-9c42-c858bf57b784	null	standard-store	10511	draft	null
fbb3b175-1a3b-4515-b0b3-0ce6d6d0145f	null	standard-store	10511	draft	null
84004999-d2cf-4af4-bfaa-c1077d1d8621	null	standard-store	10511	sent	2017-05-21
cbea0789-6886-431a-a8e1-723d5aafc7b9	null	standard-store	10511	scheduled	2019-02-12
ec8988ff-5136-4b81-b448-cd456dc487a4	null	standard-store	10511	review	2019-01-13
0e119440-5fbc-4afe-a784-a6bcfe3a6e4d	null	standard-store	10511	draft	null
98503a20-4396-4809-b3ec-8e330c15afa9	null	standard-store	10511	needs_action	2018-12-11
d33a9173-dc64-464f-8e58-49b4c9c2fdae	null	standard-store	10511	draft	null
bee0dc72-acca-44e2-82ea-d18e830f91a2	null	standard-store	10511	sent	2016-03-12

INSERT 0 15

CREATE INDEX

id	collection_id	type	affiliation_id	status	scheduled_for_date
6e4dee09-7a4e-47be-bdd8-935a67bb2063	740f6b42-bbf1-4aeb-8fe9-6874635d9e29	multi-store	12091	draft	null
1143c066-01ed-4eb5-a146-68487de702a9	bce85e31-4d2f-43b1-b263-57fca356856f	multi-store	12091	draft	null
3bb38fbd-d411-4f78-9c42-c858bf57b784	null	standard-store	10511	draft	null
fbb3b175-1a3b-4515-b0b3-0ce6d6d0145f	null	standard-store	10511	draft	null
84004999-d2cf-4af4-bfaa-c1077d1d8621	null	standard-store	10511	sent	2017-05-21
cbea0789-6886-431a-a8e1-723d5aafc7b9	null	standard-store	10511	scheduled	2019-02-12
ec8988ff-5136-4b81-b448-cd456dc487a4	null	standard-store	10511	review	2019-01-13
0e119440-5fbc-4afe-a784-a6bcfe3a6e4d	null	standard-store	10511	draft	null
98503a20-4396-4809-b3ec-8e330c15afa9	null	standard-store	10511	needs_action	2018-12-11
d33a9173-dc64-464f-8e58-49b4c9c2fdae	null	standard-store	10511	draft	null
bee0dc72-acca-44e2-82ea-d18e830f91a2	null	standard-store	10511	sent	2016-03-12

SELECT 11