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.17 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 article (
    article_id bigint generated always as identity, 
    article_name varchar(20) not null,
    constraint article_pk primary key(article_id),
    constraint article_bk unique (article_name) 
);

create table tags (
   tag_id bigint generated always as identity,
   tag_name  varchar(30), 
   constraint tags_pk primary key(tag_id), 
   constraint tags_bk unique (tag_name)    
);

create table article_tags (
    article_id  bigint,
    tag_id      bigint, 
    constraint article_tags_pk primary key (article_id, tag_id),
    constraint article_tags_2_tags_fk 
               foreign key(tag_id)
               references tags(tag_id),
    constraint article_tags_2_article_fk 
               foreign key(article_id) 
               references article(article_id)
);  
 
CREATE TABLE

CREATE TABLE

CREATE TABLE

select * from article;
select * from tags;
select * from article_tags;

article_id
article_name

SELECT 0

tag_id
tag_name

SELECT 0

article_id
tag_id

SELECT 0

create or replace 
procedure create_article_with_tags( article_name_in article.article_name%type
                                  , tags_list_in text[]
                                  ) 
  language sql 
as $$

  insert into article (article_name)
         values (article_name_in)
         on conflict (article_name) do nothing; 
        
  insert into tags (tag_name)
         select unnest(tags_list_in)
         on conflict (tag_name) do nothing;        
        
  insert into article_tags(article_id, tag_id) 
     select a.article_id, t.tag_id
       from (select article_id
               from article
              where article_name = article_name_in
             ) a
      cross join (select tag_id 
                    from tags 
                   where tag_name in (select unnest ( tags_list_in)) 
                 ) t 
      on conflict (article_id, tag_id) do nothing ; 
$$;             

CREATE PROCEDURE

              do $$
begin
   call create_article_with_tags( 'Articls #1',Array['T1', 'T2', 'T4']);
  end; 
$$;

DO

select * from article;
select * from tags;
select * from article_tags;

article_id
article_name

1
Articls #1

SELECT 1

tag_id
tag_name

1
T1

2
T2

3
T4

SELECT 3

article_id
tag_id

1
1

1
2

1
3

SELECT 3

do $$
begin
   call create_article_with_tags( 'Articls #2',Array['T1', 'T5', 'T7']);
   call create_article_with_tags( 'Articls #1',Array['T7']);  
end; 
$$; 

DO

select * from article;
select * from tags;
select * from article_tags;

article_id
article_name

1
Articls #1

2
Articls #2

SELECT 2

tag_id
tag_name

1
T1

2
T2

3
T4

5
T5

6
T7

SELECT 5

article_id
tag_id

1
1

1
2

1
3

2
5

2
1

2
6

1
6

SELECT 7

-- rebuild Article Name, Tag Array structure
select a.article_name, array_agg(t.tag_name order by t.tag_name) 
  from article_tags  ats 
  join article       a  on (a.article_id = ats.article_id)
  join tags          t  on (t.tag_id = ats.tag_id)
 group by article_name;

article_name
array_agg

Articls #1
{T1,T2,T4,T7}

Articls #2
{T1,T5,T7}

SELECT 2