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 employee (
  emp_id INTEGER,
  name TEXT,
  dept TEXT,
  PRIMARY KEY (emp_id)
);

CREATE TABLE


INSERT INTO
  employee
VALUES
  (1, 'John', 'Sales');

INSERT 0 1


SELECT * FROM employee


emp_id
name
dept


1
John
Sales



SELECT 1


MERGE INTO
  employee   AS e
USING
(
  VALUES
    (1, NULL,   'IT'),
    (2, 'Jack', 'Sales')
)
  AS t (emp_id, name, dept)
    ON e.emp_id = t.emp_id
WHEN MATCHED THEN
  UPDATE SET
    name = COALESCE(t.name, e.name),
    dept = COALESCE(t.dept, e.dept)
WHEN NOT MATCHED THEN
  INSERT
    (  emp_id,   name,   dept)
  VALUES
    (t.emp_id, t.name, t.dept);

MERGE 2


SELECT * FROM employee


emp_id
name
dept


1
John
IT

2
Jack
Sales



SELECT 2