You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
WITH
-- объединяем ограничения FK с атрибутами, которые в них задействованы
fk_with_attributes AS (
SELECTc.connameas fk_name,
c.conrelid,
c.confrelid,
fk_conkey.conkey_orderAS att_order,
fk_conkey.conkey_number,
fk_confkey.confkey_number,
rel_att.attnameAS rel_att_name,
rel_att.atttypidAS rel_att_type_id,
rel_att.atttypmodAS rel_att_type_mod,
rel_att.attnotnullAS rel_att_notnull,
frel_att.attnameAS frel_att_name,
frel_att.atttypidAS frel_att_type_id,
frel_att.atttypmodAS frel_att_type_mod,
frel_att.attnotnullAS frel_att_notnull
FROMpg_catalog.pg_constraintAS c
CROSS JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS fk_conkey(conkey_number, conkey_order)
LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS fk_confkey(confkey_number, confkey_order)
ONfk_conkey.conkey_order=fk_confkey.confkey_orderLEFT JOINpg_catalog.pg_attributeAS rel_att
ONrel_att.attrelid=c.conrelidANDrel_att.attnum=fk_conkey.conkey_numberLEFT JOINpg_catalog.pg_attributeAS frel_att
ONfrel_att.attrelid=c.confrelidANDfrel_att.attnum=fk_confkey.confkey_numberWHEREc.contypeIN ('f')
)
SELECTc.fk_name, -- наименование ограничения fkr_from.relname, -- ссылающееся отношениеc.rel_att_names, -- атрибуты в ссылающемся отношенииr_to.relname, -- целесое отношениеc.frel_att_names-- атрибуты в целевом отношенииFROM (
-- отбираем FK, у которые есть расхождения типов колонокSELECT
fk_name,
conrelid,
confrelid,
array_agg (rel_att_name order by att_order ) as rel_att_names,
array_agg (frel_att_name order by att_order ) as frel_att_names
FROM fk_with_attributes
WHERE
((rel_att_type_id <> frel_att_type_id) OR (rel_att_type_mod <> frel_att_type_mod))
GROUP BY1, 2, 3
) AS c
INNER JOINpg_catalog.pg_classAS r_from
ONr_from.oid=c.conrelidINNER JOINpg_catalog.pg_classAS r_to
ONr_to.oid=c.confrelid
The text was updated successfully, but these errors were encountered:
See https://habr.com/ru/articles/803841/
Part "Соответствие типов в ссылающейся и целевой таблицах"
The text was updated successfully, but these errors were encountered: