Postgres 13

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 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE test 
(
  s1_no INTEGER GENERATED ALWAYS AS identity,
  address TEXT NOT NULL
);
INSERT INTO test (address) 
VALUES 

--
-- my own test cases
--

('"sdfg ghhjbghjj 123456 12345678901"'),
('"dsdcfg vbggff 12345 123456789"'),      --  <<---- should NOT be returned
                                          --         no 11 digit nu.
--
-- your data (with concatenated records corrected)
--

('"16/127, Off M G Road, Goregaon (west) Mumbai, Maharashtra, 400062 02228744298"'),
('"113, 113,ragiptblr-2, Ragi Pet Bangalore, Karnataka, 560002 2229218"'),
('"2, 2, Panchal Estate, Bapunagar Ahmedabad, Gujarat, 380024 07922740143"'),
('"2nd Flr, B Wing, Jay Chamber, Service Rd, Near Telephone Exchange, Vile Parle (east) Mumbai, Maharashtra, 400057 02226104168"'),
('"New#27, Gopalakrishna Street, T Nagar Chennai, Tamil Nadu, 600017 04428150211"'),
('"9360 61/8, Multani Dhanda, Pahar Ganj Delhi, Delhi, 110055 01123550815"'),
('"113, 113,ragiptblr-2, Ragi Pet Bangalore, Karnataka,  2229218"'),

--
-- This last record from your own data, returns NULL for sstr (see below)
-- because there is no 6 digit no. in the address.
--

('"9360 61/8, Multani Dhanda, Pahar Ganj Delhi, Delhi,  01123550815"');  


10 rows affected

-- SELECT * FROM test;
SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} ')) AS sstr
FROM test
WHERE address ~ ' \d{11}"'
ORDER BY s1_no;

--
-- or you could use something like - to eliminate record 10 being returned!
--
-- WHERE SUBSTRING(address FROM ' \d{6} ') IS NOT NULL 
-- AND address ~ ' \d{12}"';
--


s1_no
sstr


1
123456

3
400062

5
380024

6
400057

7
600017

8
110055

10
null


--
-- or you could use something like - to eliminate record 10 (sstr IS NULL) being returned!
--

SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} ')) AS sstr
FROM test
WHERE SUBSTRING(address FROM ' \d{6} ') IS NOT NULL 
AND address ~ ' \d{11}"';


s1_no
sstr


1
123456

3
400062

5
380024

6
400057

7
600017

8
110055


SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} ')) AS sstr,
  PG_TYPEOF(TRIM(SUBSTRING(address FROM ' \d{6} ')))
FROM test
WHERE address ~ ' \d{11}"'
ORDER BY s1_no;


s1_no
sstr
pg_typeof


1
123456
text

3
400062
text

5
380024
text

6
400057
text

7
600017
text

8
110055
text

10
null
text


SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} '))::INT AS sstr,
  PG_TYPEOF(TRIM(SUBSTRING(address FROM ' \d{6} '))::INT)
FROM test
WHERE address ~ ' \d{11}"'
ORDER BY s1_no;


s1_no
sstr
pg_typeof


1
123456
integer

3
400062
integer

5
380024
integer

6
400057
integer

7
600017
integer

8
110055
integer

10
null
integer

s1_no	sstr	pg_typeof
1	123456	text
3	400062	text
5	380024	text
6	400057	text
7	600017	text
8	110055	text
10	null	text

s1_no	sstr	pg_typeof
1	123456	integer
3	400062	integer
5	380024	integer
6	400057	integer
7	600017	integer
8	110055	integer
10	null	integer