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


create table test( id integer, account text);

insert into test (id ,account)  
       values ( 1, '011')
            , ( 2, '11')
            , ( 3, '023')
            , ( 4, '23')
            , ( 5, '456')
            , ( 6, 'N/A')   -- Added 
            ;  
            
select * from test; 
6 rows affected



id
account


1
011

2
11

3
023

4
23

5
456

6
N/A


with num_acct (id, account) as 
     (select *
        from test 
       where account ~ '^[0-9]+$'
     )     
select row_number() over() as id  
     , na1.account as account1 
     , na2.account as account2 
  from num_acct na1
  join num_acct na2
    on (    na1.account::integer = na2.account::integer 
        and na1.id < na2.id
       );


id
account1
account2


1
011
11

2
023
23