Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE resources (
  id         SERIAL      PRIMARY KEY,
  org_id     INT         NOT NULL,
  source_id  INT         NOT NULL,
  data       JSON        NOT NULL,
  EXCLUDE USING gist
  (org_id WITH =,
  source_id WITH <>)
);

CREATE TABLE


-- id    | org_id   | source_id    | data
-- ---------------------------------------
-- 1     | 11       | 1001         | {...}
-- 2     | 22       | 2002         | {...}
-- 3     | 33       | 3003         | {...}
-- 4     | 11       | 1001         | {...}
-- 5     | 11       | 2002         | {...}  -- This should be invalid.
insert into resources 
select 1, 11, 1001, '{}'

INSERT 0 1


insert into resources 
select 2, 22, 2002, '{}'

INSERT 0 1


insert into resources 
select 3, 33, 3003, '{}'

INSERT 0 1


insert into resources 
select 4, 11, 1001, '{}'

INSERT 0 1


insert into resources 
select 5, 11, 2002, '{}'

ERROR:  conflicting key value violates exclusion constraint "resources_org_id_source_id_excl"
DETAIL:  Key (org_id, source_id)=(11, 2002) conflicts with existing key (org_id, source_id)=(11, 1001).


select * from resources


id
org_id
source_id
data


1
11
1001
{}

2
22
2002
{}

3
33
3003
{}

4
11
1001
{}



SELECT 4