Postgres 10

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?.

select version();

version

PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

CREATE TABLE service
(
  name VARCHAR (10) NOT NULL PRIMARY KEY,
  id INTEGER NOT NULL,
  active BOOLEAN NOT NULL
);

CREATE TABLE

INSERT INTO service VALUES 
('Test1', 3, true), ('Test2', 4, true), ('Test3', 5, true);

INSERT 0 3

SELECT * FROM service;

name
id
active

Test1
3
t

Test2
4
t

Test3
5
t

SELECT 3

CREATE TEMPORARY TABLE api
(
  name VARCHAR (10) NOT NULL PRIMARY KEY,
  id INTEGER NOT NULL
  -- active BOOLEAN NOT NULL 
);

CREATE TABLE

INSERT INTO api VALUES
('Test2', 4), ('Test3', 5), ('Test4', 6);

INSERT 0 3

SELECT * FROM api;

name
id

Test2
4

Test3
5

Test4
6

SELECT 3

WITH cte1 (nom1) AS 
(
  INSERT INTO service  (name, id, active) 
    SELECT a.name, a.id, true FROM api a 
    WHERE a.name NOT IN (SELECT name FROM service) RETURNING name
),
cte2 (nom2) AS
(
  UPDATE service s SET active = false 
    WHERE s.name NOT IN (SELECT name FROM api) RETURNING s.name 
)
SELECT * FROM service;

name
id
active

Test1
3
t

Test2
4
t

Test3
5
t

SELECT 3

SELECT * FROM service 
ORDER BY name;

name
id
active

Test1
3
f

Test2
4
t

Test3
5
t

Test4
6
t

SELECT 4