Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE country (
  country_id varchar(2) PRIMARY KEY
, country text UNIQUE NOT NULL
);

INSERT INTO country VALUES
  ('US', 'United States')
, ('FR', 'France')
-- etc.
;

CREATE TABLE product (
  product_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, product text NOT NULL
);

INSERT INTO product (product) VALUES
  ('Brown Shoe')
, ('Pink Flamingo')
-- etc.
;

CREATE TABLE product_ratings (
  product_id int REFERENCES product
, country_id varchar(2) REFERENCES country
, rating1 real
, rating2 real
-- more?
, PRIMARY KEY (product_id, country_id)   
);

INSERT INTO product_ratings (product_id, country_id, rating1, rating2) VALUES
  (1, 'US', 1, 2)
, (1, 'FR', 3, 4)
, (2, 'FR', 5, 6)
;

-- example index
CREATE INDEX product_ratings_country_rating1 ON product_ratings (country_id, rating1);

CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 3



CREATE INDEX


-- get product 1 with French ratings (if any)
SELECT *
FROM   product p
LEFT   JOIN product_ratings pr ON pr.product_id = p.product_id
                              AND pr.country_id = 'FR'
WHERE  p.product_id = 1;


product_id
product
product_id
country_id
rating1
rating2


1
Brown Shoe
1
FR
3
4



SELECT 1


-- get all products with French rating1 > 3 (can use example index)
SELECT *
FROM   product p
JOIN   product_ratings pr USING(product_id)
WHERE  pr.country_id = 'FR'
AND    rating1 > 3;


product_id
product
country_id
rating1
rating2


2
Pink Flamingo
FR
5
6



SELECT 1