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 |