PostgreSQL: Wykrywanie tabel bez primary key i dodawanie primary key

16-Mar-2022

Niektóre mechanizmy PostgreSQL, jak np. replikacja logiczna wymagają, aby tabele posiadały primary key. Dlatego przyda się wiedzieć, czy baza danych spełnia wymogi dla replikacji logicznej. Oto polecenie, które wyświetli informację o tabelach bez primary key:

select tab.table_schema,
       tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco 
     on tab.table_schema = tco.table_schema
        and tab.table_name = tco.table_name 
        and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema')
      and tco.constraint_name is null
order by table_schema, table_name;

select tab.table_schema,

tab.table_name

from information_schema.tables tab

left join information_schema.table_constraints tco

on tab.table_schema = tco.table_schema

and tab.table_name = tco.table_name

and tco.constraint_type = 'PRIMARY KEY'

where tab.table_type = 'BASE TABLE'

and tab.table_schema not in ('pg_catalog', 'information_schema')

and tco.constraint_name is null

order by table_schema, table_name;

I co jeśli takie tabele się znajdą? Ogólnie masz kłopot, ale w najlepszym przypadku, znajdzie się w tabelach kolumna, która może być potraktowana jako primary key. Zaczynamy więc od sprawdzenia struktur tabel:

mytest=&gt; \d towns
 Table "public.towns"
 Column     | Type                  | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+-----------------------------------
 id         | integer               |           | not null | nextval('towns_id_seq'::regclass)
 code       | character varying(10) |           | not null | 
 article    | text                  |           |          | 
 name       | text                  |           | not null | 
 department | character varying(4)  |           | not null | 
Indexes:
 "towns_code_department_key" UNIQUE CONSTRAINT, btree (code, department)
 "towns_id_key" UNIQUE CONSTRAINT, btree (id)