Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table tag_country( ctry_id   integer  generated always as identity
                        , label     text
                        , item_name text
                        ); 
                        
-- fewer but enough
insert into tag_country( label, item_name ) 
     select * 
       from ( 
               select 'France', 'Item#1' || to_char(n,'009') 
                 from generate_series(1,9) g1(n)
               union all 
               select 'Spain', 'Item#2' || to_char(n,'009') 
                 from generate_series(1,4) g2(n)
               union all  
               select null, 'Item#3' || to_char(n,'009') 
                 from generate_series(1,5) g3(n)
            ) d
        order by random () ;   
        
select *
  from tag_country; 

CREATE TABLE



INSERT 0 18




ctry_id
label
item_name


1
Spain
Item#2 001

2
Spain
Item#2 003

3
France
Item#1 005

4
null
Item#3 002

5
France
Item#1 007

6
Spain
Item#2 002

7
France
Item#1 003

8
France
Item#1 009

9
France
Item#1 001

10
null
Item#3 004

11
null
Item#3 005

12
null
Item#3 001

13
France
Item#1 002

14
null
Item#3 003

15
France
Item#1 004

16
Spain
Item#2 004

17
France
Item#1 008

18
France
Item#1 006



SELECT 18


select "<null>", "France", "Spain" 
  from ( 
        select count(*) filter (where label is null) as "<null>"
             , count(*) filter (where label = 'France') as "France"
             , count(*) filter (where label = 'Spain')  as "Spain"
          from tag_country             
       ) sq;


<null>
France
Spain


5
9
4



SELECT 1


-- but what happens when Portugal gets added 
insert into tag_country( label, item_name ) 
    select 'Portugal', 'Item#1' || to_char(n,'009') 
      from generate_series(1,7) gp(n) ; 

INSERT 0 7


-- without updating, basically nothing 
-- fewer but enough
insert into tag_country( label, item_name ) 
     select * 
       from ( 
               select 'France', 'Item#1' || to_char(n,'009') 
                 from generate_series(1,9) g1(n)
               union all 
               select 'Spain', 'Item#2' || to_char(n,'009') 
                 from generate_series(1,4) g2(n)
               union all  
               select null, 'Item#3' || to_char(n,'009') 
                 from generate_series(1,5) g3(n)
            ) d
        order by random () ;   
        

INSERT 0 18


-- but updating the query and all is good
select "<null>", "France", "Spain", "Portugal"
  from ( 
        select count(*) filter (where label is null) as "<null>"
             , count(*) filter (where label = 'France') as "France"
             , count(*) filter (where label = 'Portugal')  as "Portugal"
             , count(*) filter (where label = 'Spain')  as "Spain"
          from tag_country             
       ) sq;


<null>
France
Spain
Portugal


10
18
8
7



SELECT 1


-- but jast a simple count and give it to the Presentation Manger
select label, count(*)
  from tag_country 
 group by label
 order by label nulls first;


label
count


null
10

France
18

Portugal
7

Spain
8



SELECT 4


-- and any further update is automatically picked-up
insert into tag_country( label, item_name ) 
    select 'Germany', 'Item#4' || to_char(n,'009') 
      from generate_series(1,3) gp(n) ; 

INSERT 0 3


select label, count(*)
  from tag_country 
 group by label
 order by label nulls first;


label
count


null
10

France
18

Germany
3

Portugal
7

Spain
8



SELECT 5

ctry_id	label	item_name
1	Spain	Item#2 001
2	Spain	Item#2 003
3	France	Item#1 005
4	null	Item#3 002
5	France	Item#1 007
6	Spain	Item#2 002
7	France	Item#1 003
8	France	Item#1 009
9	France	Item#1 001
10	null	Item#3 004
11	null	Item#3 005
12	null	Item#3 001
13	France	Item#1 002
14	null	Item#3 003
15	France	Item#1 004
16	Spain	Item#2 004
17	France	Item#1 008
18	France	Item#1 006