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.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE test
(
  ip_v6 VARCHAR (39) NOT NULL, 
  host VARCHAR (100) NOT NULL,

  ip_v6_token_1 TEXT 
    GENERATED ALWAYS AS   
      (REGEXP_REPLACE (ip_v6, '(^[[:xdigit:]]{1,4})(.*$)', '\1')) STORED,
  ip_v6_token_2 TEXT 
    GENERATED ALWAYS AS 
      (REGEXP_REPLACE (ip_v6, '(^[[:xdigit:]]{1,4}:)([[:xdigit:]]{1,4})(.*$)', '\2')) STORED,

  -- as in simple solution, same for tokens 3-7 as per requirements!

  CONSTRAINT test_ip_v6_pk PRIMARY KEY (ip_v6),
  CONSTRAINT ip_v6_regexp_ck 
    CHECK(ip_v6 ~ '^([[:xdigit:]]{1,4}:){7,7}[[:xdigit:]]{1,4}$'),
  CONSTRAINT test_ip_v6_token_1_uq UNIQUE (ip_v6_token_1),
  CONSTRAINT test_ip_v6_token_2_uq UNIQUE (ip_v6_token_2)
);
CREATE INDEX test_ip_v6_token_1_ix ON test USING BTREE (ip_v6_token_1);
CREATE INDEX test_ip_v6_token_2_ix ON test USING BTREE (ip_v6_token_2);
INSERT INTO test (ip_v6, host) 
VALUES 
('2001:0db8:0000:FFF0:0000:8a2e:0370:7334', 'host_1'),
('10:9:8:7:6:6:7:6'                       , 'host_2');
2 rows affected

--
-- This is expected to fail because of the CHECK (IS_IPV6(ip_v6)) constraint.
-- It fails on the second element XXXX.
--

INSERT INTO test (ip_v6, host) 
VALUES 
('4001:XXXX:2222:FFF0:0000:8a2e:0370:7334', 'host_3');
ERROR:  new row for relation "test" violates check constraint "ip_v6_regexp_ck"
DETAIL:  Failing row contains (4001:XXXX:2222:FFF0:0000:8a2e:0370:7334, host_3, 4001, 4001:XXXX:2222:FFF0:0000:8a2e:0370:7334).


INSERT INTO test (ip_v6, host) 
VALUES 
('4001:1111:2222:FFF0:0000:8a2e:0370:7334', 'host_3');
1 rows affected

SELECT 
  ip_v6_token_1 AS "Token 1",
  ip_v6_token_2 AS "Token 2",
  ip_v6         AS "IPv6:",
  host          AS "Host:"
FROM test ORDER BY host;


Token 1
Token 2
IPv6:
Host:


2001
0db8
2001:0db8:0000:FFF0:0000:8a2e:0370:7334
host_1

10
9
10:9:8:7:6:6:7:6
host_2

4001
1111
4001:1111:2222:FFF0:0000:8a2e:0370:7334
host_3


CREATE TABLE test_fk
(
  t_id    SERIAL NOT NULL PRIMARY KEY,
  token_1 VARCHAR (4) NOT NULL,
  token_2 VARCHAR (4),
  
  --
  -- Other fields
  --
  
  CONSTRAINT test_fk_test_ip_v6_token_1_fk 
    FOREIGN KEY (token_1) 
      REFERENCES test (ip_v6_token_1) ON DELETE CASCADE ON UPDATE CASCADE,
  
  CONSTRAINT test_fk_test_ip_v6_token_2_fk 
    FOREIGN KEY (token_2) 
      REFERENCES test (ip_v6_token_2) ON DELETE CASCADE ON UPDATE CASCADE
);
INSERT INTO test_fk (token_1, token_2)
VALUES 
('2001', '9'), ('2001', '9'), ('2001', '1111'),
('10', '1111'), ('4001', '0db8'), ('2001', '9'); 
SELECT * FROM test_fk;
6 rows affected



t_id
token_1
token_2


1
2001
9

2
2001
9

3
2001
1111

4
10
1111

5
4001
0db8

6
2001
9


DELETE FROM test WHERE host = 'host_3';
1 rows affected

SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
9

2
2001
9

6
2001
9


--
-- This time we expect a valid IPv6 address to fail because of the consecutive colon
-- (:) characters - this is valid for IPv6, but not for the regexp CHECK constraint
--

UPDATE test SET ip_v6 = '123:123::123' WHERE ip_v6 = '10:9:8:7:6:6:7:6';
ERROR:  new row for relation "test" violates check constraint "ip_v6_regexp_ck"
DETAIL:  Failing row contains (123:123::123, host_2, 123, 123).


--
-- This passes
--

UPDATE test SET ip_v6 = '987:654:321:123:123:123:123:123' 
  WHERE ip_v6 = '10:9:8:7:6:6:7:6';
1 rows affected

SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
654

2
2001
654

6
2001
654


SELECT * FROM test;


ip_v6
host
ip_v6_token_1
ip_v6_token_2


2001:0db8:0000:FFF0:0000:8a2e:0370:7334
host_1
2001
0db8

987:654:321:123:123:123:123:123
host_2
987
654


INSERT INTO test_fk (token_1, token_2) VALUES ('2001', '0db8');
1 rows affected

SELECT * FROM test_fk;


t_id
token_1
token_2


1
2001
654

2
2001
654

6
2001
654

7
2001
0db8


--
-- Fails as expected - there is no value 5432 in any corresponding place in the 
-- ip_v6 field in the test table.
--

INSERT INTO test_fk (token_1, token_2) VALUES ('2001', '5432');
ERROR:  insert or update on table "test_fk" violates foreign key constraint "test_fk_test_ip_v6_token_2_fk"
DETAIL:  Key (token_2)=(5432) is not present in table "test".


--
--
--
--
-- Regular expression tests
--
--
-- should work but doesn't for MySQL - works for PostgreSQL 
--

SELECT REGEXP_REPLACE(ip_v6, '^\d{1,4}', 'YYYYY') FROM test;  


regexp_replace


YYYYY:0db8:0000:FFF0:0000:8a2e:0370:7334

YYYYY:654:321:123:123:123:123:123


--
-- should work but doesn't for MySQL 
--

SELECT REGEXP_REPLACE(ip_v6, '[[:digit:]]{1,4}', '123') FROM test;  


regexp_replace


123:0db8:0000:FFF0:0000:8a2e:0370:7334

123:654:321:123:123:123:123:123


SELECT REGEXP_REPLACE(ip_v6, '[[:xdigit:]]', '') FROM test;


regexp_replace


001:0db8:0000:FFF0:0000:8a2e:0370:7334

87:654:321:123:123:123:123:123


SELECT REGEXP_REPLACE('2001:1001', '[[:xdigit:]]', '', 'g') AS "RR_test";


RR_test


:


--
-- Should work but fails for MySQL! Worse than simply failing, it returns some sort
-- of result which appears to be just the string literals '3', '2' & '1' - potentially
-- the source of very difficult to trace errors.
--

SELECT 
--  REGEXP_REPLACE(ip_v6, '(^[[:xdigit:]]{1,4}):(.*$)', '\1') 
--  AS "Rep text 1",
--  REGEXP_REPLACE(ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\3   \2   \1') 
--  AS "Rep text",
  REGEXP_REPLACE(ip_v6, 
  '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\4 xx \3   \2   \1') 
  AS "Rep text 2",
  ip_v6,
  REGEXP_REPLACE(ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\3   \2   \1') 
  AS "Rep text 3",
  ip_v6
FROM test;


Rep text 2
ip_v6
Rep text 3
ip_v6


FFF0:0000:8a2e:0370:7334 xx 0000   0db8   2001
2001:0db8:0000:FFF0:0000:8a2e:0370:7334
0000:FFF0:0000:8a2e:0370:7334   0db8   2001
2001:0db8:0000:FFF0:0000:8a2e:0370:7334

123:123:123:123:123 xx 321   654   987
987:654:321:123:123:123:123:123
321:123:123:123:123:123   654   987
987:654:321:123:123:123:123:123


SELECT
  REGEXP_REPLACE
  (
    ip_v6, '(^[[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):([[:xdigit:]]{1,4}):(.*$)', '\4 xx \3   \2   \1'
  ) 
  AS "Rep text 2",
  ip_v6
FROM test;


Rep text 2
ip_v6


FFF0:0000:8a2e:0370:7334 xx 0000   0db8   2001
2001:0db8:0000:FFF0:0000:8a2e:0370:7334

123:123:123:123:123 xx 321   654   987
987:654:321:123:123:123:123:123
Token 1	Token 2	IPv6:	Host:
2001	0db8	2001:0db8:0000:FFF0:0000:8a2e:0370:7334	host_1
10	9	10:9:8:7:6:6:7:6	host_2
4001	1111	4001:1111:2222:FFF0:0000:8a2e:0370:7334	host_3
Rep text 2	ip_v6	Rep text 3	ip_v6
FFF0:0000:8a2e:0370:7334 xx 0000 0db8 2001	2001:0db8:0000:FFF0:0000:8a2e:0370:7334	0000:FFF0:0000:8a2e:0370:7334 0db8 2001	2001:0db8:0000:FFF0:0000:8a2e:0370:7334
123:123:123:123:123 xx 321 654 987	987:654:321:123:123:123:123:123	321:123:123:123:123:123 654 987	987:654:321:123:123:123:123:123