我有pgAdmin III。
有没有一种方法可以自动执行此操作,而不是手动检查每个表?
#1 楼
您可以在如下查询中使用函数pg_get_constraintdef(constraint_oid)
:SELECT conrelid::regclass AS table_from
, conname
, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype IN ('f', 'p ')
AND connamespace = 'public'::regnamespace -- your schema here
ORDER BY conrelid::regclass::text, contype DESC;
结果:
table_from | conname | pg_get_constraintdef
------------+------------+----------------------
tbl | tbl_pkey | PRIMARY KEY (tbl_id)
tbl | tbl_col_fk | FOREIGN KEY (col) REFERENCES tbl2(col) ON UPDATE CASCADE
...
返回给定架构中所有表的所有主键和外键,按表名排序,先按PK排序。
有关
pg_constraint
的手册。 regclass
,...)。#2 楼
基于Erwin解决方案:SELECT conrelid::regclass AS "FK_Table"
,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), 14, position(')' in pg_get_constraintdef(c.oid))-14) END AS "FK_Column"
,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position(' REFERENCES ' in pg_get_constraintdef(c.oid))+12, position('(' in substring(pg_get_constraintdef(c.oid), 14))-position(' REFERENCES ' in pg_get_constraintdef(c.oid))+1) END AS "PK_Table"
,CASE WHEN pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %' THEN substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14, position(')' in substring(pg_get_constraintdef(c.oid), position('(' in substring(pg_get_constraintdef(c.oid), 14))+14))-1) END AS "PK_Column"
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype IN ('f', 'p ')
AND pg_get_constraintdef(c.oid) LIKE 'FOREIGN KEY %'
ORDER BY pg_get_constraintdef(c.oid), conrelid::regclass::text, contype DESC;
将返回表格形式:
| FK_Table | FK_Column | PK_Table | PK_Column |
评论
这几乎是HeidiSQL检索外键定义所需要的。有没有办法以更少的LIKE,SUBSTRING和POSITION调用来使操作更优雅?我快到了,但是查询列出了两次列。
– Anse
20年1月30日在12:03
#3 楼
无需解析pg_get_constraintdef()
,只需使用pg_constraint
表中的列即可获取其他详细信息(文档)。这里
constraint_type
可以是: ,f-外键,
u-唯一,
c-检查约束,
x-排除,
...
根据Erwin的回答:
SELECT c.conname AS constraint_name,
c.contype AS constraint_type,
sch.nspname AS "self_schema",
tbl.relname AS "self_table",
ARRAY_AGG(col.attname ORDER BY u.attposition) AS "self_columns",
f_sch.nspname AS "foreign_schema",
f_tbl.relname AS "foreign_table",
ARRAY_AGG(f_col.attname ORDER BY f_u.attposition) AS "foreign_columns",
pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
LEFT JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS u(attnum, attposition) ON TRUE
LEFT JOIN LATERAL UNNEST(c.confkey) WITH ORDINALITY AS f_u(attnum, attposition) ON f_u.attposition = u.attposition
JOIN pg_class tbl ON tbl.oid = c.conrelid
JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
LEFT JOIN pg_attribute col ON (col.attrelid = tbl.oid AND col.attnum = u.attnum)
LEFT JOIN pg_class f_tbl ON f_tbl.oid = c.confrelid
LEFT JOIN pg_namespace f_sch ON f_sch.oid = f_tbl.relnamespace
LEFT JOIN pg_attribute f_col ON (f_col.attrelid = f_tbl.oid AND f_col.attnum = f_u.attnum)
GROUP BY constraint_name, constraint_type, "self_schema", "self_table", definition, "foreign_schema", "foreign_table"
ORDER BY "self_schema", "self_table";
结果由
schema
和table
排序。技术说明:请参阅有关
with ordinality
的问题。 >评论
我想知道如何检索引用的列及其表和架构,而不必解析pg_get_constraintdef()的结果?
– Anse
20-2-2在19:28
@Anse更新了答案。谢谢你的主意。
– Evgeny Nozdrev
20年2月8日在15:50
#4 楼
最近不得不为基于信息模式构建CRUD实用程序的数据访问层实现此功能,最终解决了这个问题。SELECT
current_schema() AS "schema",
current_catalog AS "database",
"pg_constraint".conrelid::regclass::text AS "primary_table_name",
"pg_constraint".confrelid::regclass::text AS "foreign_table_name",
(
string_to_array(
(
string_to_array(
pg_get_constraintdef("pg_constraint".oid),
'('
)
)[2],
')'
)
)[1] AS "foreign_column_name",
"pg_constraint".conindid::regclass::text AS "constraint_name",
TRIM((
string_to_array(
pg_get_constraintdef("pg_constraint".oid),
'('
)
)[1]) AS "constraint_type",
pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"
FROM pg_constraint AS "pg_constraint"
JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace
WHERE
"pg_constraint".contype IN ( 'f', 'p' )
AND
"pg_namespace".nspname = current_schema()
AND
"pg_constraint".conrelid::regclass::text IN ('whatever_table_name')
评论
此修饰符在条件也返回任何唯一约束的地方:WHERE子类型IN('f','p','u')
–丹尼尔·沃尔特里普(Daniel Waltrip)
18年6月17日,0:34