Postgres 16

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 double_uniq(uniq1 int primary key, uniq2 int UNIQUE, val text);

INSERT INTO double_uniq VALUES
  (1,1,'a')
, (2,2,'b')
;

CREATE TABLE

INSERT 0 2

CREATE OR REPLACE FUNCTION f_upsert_double(_uniq1 int, _uniq2 int, _val text)
  RETURNS void 
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      -- covers **all** unique violations
      INSERT INTO double_uniq
             ( uniq1,  uniq2,  val) 
      VALUES (_uniq1, _uniq2, _val) 
      ON     CONFLICT DO NOTHING;

      EXIT WHEN FOUND;

      -- updating *two* rows is a corner case possibility!
      UPDATE double_uniq
      SET    val = _val
      WHERE  uniq1 = _uniq1
      OR     uniq2 = _uniq2;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;

CREATE FUNCTION

SELECT f_upsert_double(3,4,'c');
SELECT f_upsert_double(3,2,'b1');
SELECT f_upsert_double(1,2,'b2');

f_upsert_double

SELECT 1

f_upsert_double

SELECT 1

f_upsert_double

SELECT 1

TABLE double_uniq ORDER BY uniq1, uniq2;

uniq1
uniq2
val

1
1
b2

2
2
b2

3
4
b1

SELECT 3