Postgres 12

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 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE   keys 
(
  key_name   VARCHAR(128),

  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,  -- changed to "pos" - position is a keyword
                                  -- https://www.postgresql.org/docs/12/sql-keywords-appendix.html
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

  marker TEXT NOT NULL,

  CONSTRAINT keys_pk PRIMARY KEY (key_name)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ctxt_pos_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);
INSERT INTO keys (key_name, context, pos, marker)
VALUES
('A.1', 'ctx_A', 0, 'marker 1 initial'), 
('A.2', 'ctx_A', 1, 'marker 2 initial'), 
('A.3', 'ctx_A', 2, 'marker 3 initial'), 
('A.4', 'ctx_A', 3, 'marker 4 initial'),
('B.1', 'ctx_B', 0, 'marker 5 initial'), 
('B.2', 'ctx_B', 1, 'marker 6 initial'), 
('B.3', 'ctx_B', 2, 'marker 7 initial'), 
('B.4', 'ctx_B', 3, 'marker 8 initial');
8 rows affected

BEGIN TRANSACTION;
  UPDATE keys
    SET 
      key_name = 
        LEFT(key_name, STRPOS(key_name, '.')) || ((SPLIT_PART(key_name, '.', 2)::INT + 1))::TEXT,
      context  = 'ctx_A',
      pos = pos + 1
    WHERE LEFT(key_name, STRPOS(key_name, '.')) = 'A.';
  INSERT INTO keys (key_name, context, pos, marker)
    VALUES ('A.1', 'ctx_A_new', 0, 'marker_new');
COMMIT;
4 rows affected

1 rows affected

SELECT * FROM keys ORDER BY key_name;


key_name
context
pos
created_at
marker


A.1
ctx_A_new
0
2021-10-28 20:31:44.800758+01
marker_new

A.2
ctx_A
1
2021-10-28 20:31:44.789842+01
marker 1 initial

A.3
ctx_A
2
2021-10-28 20:31:44.789842+01
marker 2 initial

A.4
ctx_A
3
2021-10-28 20:31:44.789842+01
marker 3 initial

A.5
ctx_A
4
2021-10-28 20:31:44.789842+01
marker 4 initial

B.1
ctx_B
0
2021-10-28 20:31:44.789842+01
marker 5 initial

B.2
ctx_B
1
2021-10-28 20:31:44.789842+01
marker 6 initial

B.3
ctx_B
2
2021-10-28 20:31:44.789842+01
marker 7 initial

B.4
ctx_B
3
2021-10-28 20:31:44.789842+01
marker 8 initial


CREATE TABLE   keys_bis 
(
  key_alpha TEXT         NOT NULL, 

  key_num   INTEGER      NOT NULL,
  
  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,

  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  
  marker TEXT NOT NULL,  -- this is for tracing purposes

  -- It's up to you to ensure that the key_name field is valid!

  CONSTRAINT keys_bis_pk PRIMARY KEY (key_alpha, key_num)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ctxt_pos_bis_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);
INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
VALUES
('A.', 1, 'ctx_A', 0, 'marker 1 initial'), 
('A.', 2, 'ctx_A', 1, 'marker 2 initial'), 
('A.', 3, 'ctx_A', 2, 'marker 3 initial'), 
('A.', 4, 'ctx_A', 3, 'marker 4 initial'),
('B.', 1, 'ctx_B', 0, 'marker 5 initial'), 
('B.', 2, 'ctx_B', 1, 'marker 6 initial'), 
('B.', 3, 'ctx_B', 2, 'marker 7 initial'), 
('B.', 4, 'ctx_B', 3, 'marker 8 initial');
8 rows affected

SELECT * FROM keys_bis;


key_alpha
key_num
context
pos
created_at
marker


A.
1
ctx_A
0
2021-10-28 20:31:44.892875+01
marker 1 initial

A.
2
ctx_A
1
2021-10-28 20:31:44.892875+01
marker 2 initial

A.
3
ctx_A
2
2021-10-28 20:31:44.892875+01
marker 3 initial

A.
4
ctx_A
3
2021-10-28 20:31:44.892875+01
marker 4 initial

B.
1
ctx_B
0
2021-10-28 20:31:44.892875+01
marker 5 initial

B.
2
ctx_B
1
2021-10-28 20:31:44.892875+01
marker 6 initial

B.
3
ctx_B
2
2021-10-28 20:31:44.892875+01
marker 7 initial

B.
4
ctx_B
3
2021-10-28 20:31:44.892875+01
marker 8 initial


BEGIN TRANSACTION;
  UPDATE keys_bis 
    SET 
      key_num = key_num + 1,
      context = 'ctx_A',
      pos   = pos + 1
    WHERE key_alpha = 'A.';
  INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
  VALUES ('A.', 1, 'ctx_A_new', 0, 'marker_new');
COMMIT;
4 rows affected

1 rows affected

SELECT * FROM keys_bis ORDER BY key_alpha, key_num;


key_alpha
key_num
context
pos
created_at
marker


A.
1
ctx_A_new
0
2021-10-28 20:31:44.904982+01
marker_new

A.
2
ctx_A
1
2021-10-28 20:31:44.892875+01
marker 1 initial

A.
3
ctx_A
2
2021-10-28 20:31:44.892875+01
marker 2 initial

A.
4
ctx_A
3
2021-10-28 20:31:44.892875+01
marker 3 initial

A.
5
ctx_A
4
2021-10-28 20:31:44.892875+01
marker 4 initial

B.
1
ctx_B
0
2021-10-28 20:31:44.892875+01
marker 5 initial

B.
2
ctx_B
1
2021-10-28 20:31:44.892875+01
marker 6 initial

B.
3
ctx_B
2
2021-10-28 20:31:44.892875+01
marker 7 initial

B.
4
ctx_B
3
2021-10-28 20:31:44.892875+01
marker 8 initial


--
-- A few ideas from https://stackoverflow.com/a/51193857/470530
--

WITH test AS
(
  SELECT 'AASDFSD.435434' AS v
  UNION ALL SELECT 'SDXVZ.343534'
  UNION ALL SELECT 'AEE#3434$%$^%^&^&^&^&AAL.12345'
)
SELECT
  v,
  RIGHT(v, LENGTH(v) - STRPOS(v, '.')),
  SUBSTRING(v FROM '^[A-Z]+[\.]'),  -- will pick AA., BBB., CCSXDEER., 
  SUBSTRING(v FROM '\d+$'),
  (select (regexp_matches(v, '[^A,]+', 'g'))[1] offset 0 limit 1) as c1
FROM
  test;


v
right
substring
substring
c1


AASDFSD.435434
435434
AASDFSD.
435434
SDFSD.435434

SDXVZ.343534
343534
SDXVZ.
343534
SDXVZ.343534

AEE#3434$%$^%^&^&^&^&AAL.12345
12345
null
12345
EE#3434$%$^%^&^&^&^&


SELECT * FROM keys_bis ORDER BY key_alpha, key_num;


key_alpha
key_num
context
pos
created_at
marker


A.
1
ctx_A_new
0
2021-10-28 20:31:44.904982+01
marker_new

A.
2
ctx_A
1
2021-10-28 20:31:44.892875+01
marker 1 initial

A.
3
ctx_A
2
2021-10-28 20:31:44.892875+01
marker 2 initial

A.
4
ctx_A
3
2021-10-28 20:31:44.892875+01
marker 3 initial

A.
5
ctx_A
4
2021-10-28 20:31:44.892875+01
marker 4 initial

B.
1
ctx_B
0
2021-10-28 20:31:44.892875+01
marker 5 initial

B.
2
ctx_B
1
2021-10-28 20:31:44.892875+01
marker 6 initial

B.
3
ctx_B
2
2021-10-28 20:31:44.892875+01
marker 7 initial

B.
4
ctx_B
3
2021-10-28 20:31:44.892875+01
marker 8 initial


SELECT
  LEFT(key_name, STRPOS(key_name, '.')), RIGHT(key_name, STRPOS(key_name, '.') -1),
  SPLIT_PART(key_name, '.', 2),
  SUBSTRING(key_name FROM '^[A-Z]+[\.]'),  -- will pick AA., BBB., CCSXDEER., 
  UNNEST(REGEXP_MATCHES(key_name, '\d+$'))::INT
FROM
  keys;


left
right
split_part
substring
unnest


B.
1
1
B.
1

B.
2
2
B.
2

B.
3
3
B.
3

B.
4
4
B.
4

A.
2
2
A.
2

A.
3
3
A.
3

A.
4
4
A.
4

A.
5
5
A.
5

A.
1
1
A.
1


BEGIN TRANSACTION;
  UPDATE keys_bis 
    SET 
      key_num = key_num + 1,
      context = 'ctx_A',
      pos   = pos + 1
    WHERE (key_alpha = 'A.' AND pos >= 2);
  INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
  VALUES ('A.', 3, 'ctx_A_new_3', 3, 'marker_new_3');
COMMIT;
3 rows affected

1 rows affected

SELECT * FROM keys_bis;


key_alpha
key_num
context
pos
created_at
marker


B.
1
ctx_B
0
2021-10-28 20:31:44.892875+01
marker 5 initial

B.
2
ctx_B
1
2021-10-28 20:31:44.892875+01
marker 6 initial

B.
3
ctx_B
2
2021-10-28 20:31:44.892875+01
marker 7 initial

B.
4
ctx_B
3
2021-10-28 20:31:44.892875+01
marker 8 initial

A.
2
ctx_A
1
2021-10-28 20:31:44.892875+01
marker 1 initial

A.
1
ctx_A_new
0
2021-10-28 20:31:44.904982+01
marker_new

A.
4
ctx_A
3
2021-10-28 20:31:44.892875+01
marker 2 initial

A.
5
ctx_A
4
2021-10-28 20:31:44.892875+01
marker 3 initial

A.
6
ctx_A
5
2021-10-28 20:31:44.892875+01
marker 4 initial

A.
3
ctx_A_new_3
3
2021-10-28 20:31:44.917597+01
marker_new_3

key_name	context	pos	created_at	marker
A.1	ctx_A_new	0	2021-10-28 20:31:44.800758+01	marker_new
A.2	ctx_A	1	2021-10-28 20:31:44.789842+01	marker 1 initial
A.3	ctx_A	2	2021-10-28 20:31:44.789842+01	marker 2 initial
A.4	ctx_A	3	2021-10-28 20:31:44.789842+01	marker 3 initial
A.5	ctx_A	4	2021-10-28 20:31:44.789842+01	marker 4 initial
B.1	ctx_B	0	2021-10-28 20:31:44.789842+01	marker 5 initial
B.2	ctx_B	1	2021-10-28 20:31:44.789842+01	marker 6 initial
B.3	ctx_B	2	2021-10-28 20:31:44.789842+01	marker 7 initial
B.4	ctx_B	3	2021-10-28 20:31:44.789842+01	marker 8 initial

key_alpha	key_num	context	pos	created_at	marker
A.	1	ctx_A	0	2021-10-28 20:31:44.892875+01	marker 1 initial
A.	2	ctx_A	1	2021-10-28 20:31:44.892875+01	marker 2 initial
A.	3	ctx_A	2	2021-10-28 20:31:44.892875+01	marker 3 initial
A.	4	ctx_A	3	2021-10-28 20:31:44.892875+01	marker 4 initial
B.	1	ctx_B	0	2021-10-28 20:31:44.892875+01	marker 5 initial
B.	2	ctx_B	1	2021-10-28 20:31:44.892875+01	marker 6 initial
B.	3	ctx_B	2	2021-10-28 20:31:44.892875+01	marker 7 initial
B.	4	ctx_B	3	2021-10-28 20:31:44.892875+01	marker 8 initial

key_alpha	key_num	context	pos	created_at	marker
A.	1	ctx_A_new	0	2021-10-28 20:31:44.904982+01	marker_new
A.	2	ctx_A	1	2021-10-28 20:31:44.892875+01	marker 1 initial
A.	3	ctx_A	2	2021-10-28 20:31:44.892875+01	marker 2 initial
A.	4	ctx_A	3	2021-10-28 20:31:44.892875+01	marker 3 initial
A.	5	ctx_A	4	2021-10-28 20:31:44.892875+01	marker 4 initial
B.	1	ctx_B	0	2021-10-28 20:31:44.892875+01	marker 5 initial
B.	2	ctx_B	1	2021-10-28 20:31:44.892875+01	marker 6 initial
B.	3	ctx_B	2	2021-10-28 20:31:44.892875+01	marker 7 initial
B.	4	ctx_B	3	2021-10-28 20:31:44.892875+01	marker 8 initial

v	right	substring	substring	c1
AASDFSD.435434	435434	AASDFSD.	435434	SDFSD.435434
SDXVZ.343534	343534	SDXVZ.	343534	SDXVZ.343534
AEE#3434$%$^%^&^&^&^&AAL.12345	12345	null	12345	EE#3434$%$^%^&^&^&^&

left	right	split_part	substring	unnest
B.	1	1	B.	1
B.	2	2	B.	2
B.	3	3	B.	3
B.	4	4	B.	4
A.	2	2	A.	2
A.	3	3	A.	3
A.	4	4	A.	4
A.	5	5	A.	5
A.	1	1	A.	1

left	right	split_part	substring	unnest
B.	1	1	B.	1
B.	2	2	B.	2
B.	3	3	B.	3
B.	4	4	B.	4
A.	2	2	A.	2
A.	3	3	A.	3
A.	4	4	A.	4
A.	5	5	A.	5
A.	1	1	A.	1

left	right	split_part	substring	unnest
B.	1	1	B.	1
B.	2	2	B.	2
B.	3	3	B.	3
B.	4	4	B.	4
A.	2	2	A.	2
A.	3	3	A.	3
A.	4	4	A.	4
A.	5	5	A.	5
A.	1	1	A.	1