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 tbl (
  id serial PRIMARY KEY
, number integer
);

INSERT INTO tbl (number) VALUES
  (1), (2), (2)
, (3), (4), (4), (4)
, (5), (13), (13)
, (17)
RETURNING *;

CREATE TABLE




id
number


1
1

2
2

3
2

4
3

5
4

6
4

7
4

8
5

9
13

10
13

11
17



INSERT 0 11


-- duplicates
SELECT id, number
FROM (
   SELECT *, lag(number) OVER (ORDER BY number) AS last_num
   FROM   tbl
   ) dup
WHERE  dup.last_num = dup.number;


id
number


3
2

6
4

7
4

10
13



SELECT 4


-- free numbers
SELECT number
FROM  (SELECT generate_series(min(number), max(number) + 10) FROM tbl) n(number)
LEFT  JOIN tbl t USING (number)
WHERE t.number IS NULL;


number


6

7

8

9

10

11

12

14

15

16

18

19

20

21

22

23

24

25

26

27



SELECT 20


DO
$do$
DECLARE
   _id int;
   _number int;
BEGIN
   CREATE TEMP TABLE free ON COMMIT DROP AS
   SELECT number
   FROM  (SELECT generate_series(min(number), max(number)) FROM tbl) n(number)
   LEFT  JOIN tbl t USING (number)
   WHERE t.number IS NULL;

   -- (only) if table is big, add an index
   CREATE INDEX ON  pg_temp.free (number);
   
   FOR _id, _number IN
      SELECT id, number
      FROM (
         SELECT *, lag(number) OVER (ORDER BY number) AS last_num
         FROM   tbl
         ) dup
      WHERE dup.last_num = dup.number
   LOOP
      WITH del AS (
         DELETE FROM pg_temp.free f
         USING (
            SELECT f1.number
            FROM   pg_temp.free f1
            WHERE  f1.number > _number
            ORDER  BY f1.number 
            LIMIT  1
            ) d
         WHERE f.number = d.number
         RETURNING f.number
         )
      UPDATE tbl t
      SET    number = d.number
      FROM   del d
      WHERE  t.id = _id;
   END LOOP;
END
$do$;

DO


TABLE tbl ORDER BY number;


id
number


1
1

2
2

4
3

5
4

8
5

3
6

6
7

7
8

9
13

10
14

11
17



SELECT 11