Postgres 11

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 (
  EmpID INTEGER,
  Dept_No INTEGER,
  Join_Date TIMESTAMP,
  termination_date TIMESTAMP,
  ID INTEGER
);

INSERT INTO employee (EmpID, Dept_No, Join_Date, termination_date, ID) VALUES
  ('1001', '11', '2020-01-01', '2020-02-01', '183'),
  ('1001', '12', '2020-02-02', '2020-04-01', '182'),
  ('1001', '11', '2020-04-02', null, '186');

CREATE TABLE



INSERT 0 3


SELECT * FROM employee


empid
dept_no
join_date
termination_date
id


1001
11
2020-01-01 00:00:00
2020-02-01 00:00:00
183

1001
12
2020-02-02 00:00:00
2020-04-01 00:00:00
182

1001
11
2020-04-02 00:00:00
null
186



SELECT 3


UPDATE employee AS e1
SET Join_Date = e1.Join_Date -INTERVAL '1 DAY'
WHERE EXISTS (
  SELECT 1 
  FROM employee e2 
  WHERE e2.EmpID = e1.EmpID AND e2.termination_date IS NOT NULL
    AND e2.Join_Date < e1.Join_Date 
)

UPDATE 2


SELECT * FROM employee


empid
dept_no
join_date
termination_date
id


1001
11
2020-01-01 00:00:00
2020-02-01 00:00:00
183

1001
12
2020-02-01 00:00:00
2020-04-01 00:00:00
182

1001
11
2020-04-01 00:00:00
null
186



SELECT 3

empid	dept_no	join_date	termination_date	id
1001	11	2020-01-01 00:00:00	2020-02-01 00:00:00	183
1001	12	2020-02-02 00:00:00	2020-04-01 00:00:00	182
1001	11	2020-04-02 00:00:00	null	186