Postgres 14

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 maven_module (id serial PRIMARY KEY, artifact_id text UNIQUE);
INSERT INTO maven_module(artifact_id) VALUES ('artifact_id_example_1');

1 rows affected

Select * from maven_module

id
artifact_id

1
artifact_id_example_1

CREATE OR REPLACE FUNCTION custom_upsert(artifact_id_input text, OUT pkOutput int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      SELECT id
      FROM   maven_module
      WHERE  artifact_id = artifact_id_input
      INTO   pkOutput;

      EXIT WHEN FOUND;

      INSERT INTO maven_module AS m (artifact_id)
      VALUES (artifact_id_input)
      ON     CONFLICT (artifact_id) DO NOTHING
      RETURNING m.id
      INTO   pkOutput;

      EXIT WHEN FOUND;
   END LOOP;
END
$func$;
-- returns new artifact_id
SELECT custom_upsert('artifact_id_example_2');

-- returns existing artifact_id
SELECT custom_upsert('artifact_id_example_1');  

SELECT custom_upsert(t)
FROM (VALUES ('artifact_id_example_1'), ('artifact_id_example_2'), ('artifact_id_example_3')) t(t);

custom_upsert

2

custom_upsert

1

custom_upsert

1

2

3

SELECT * FROM maven_module;

id
artifact_id

1
artifact_id_example_1

2
artifact_id_example_2

3
artifact_id_example_3

id	artifact_id
1	artifact_id_example_1
2	artifact_id_example_2
3	artifact_id_example_3