目的是允许插入脚本将数据添加到架构中的所有表，这样它就不会在约束中产生任何冲突。我从informationschema.tables中获取表，从informationchema.tableconstraints中获取约束，但不知道如何比较以按外键约束的顺序对表进行排序。请帮忙。
下面会产生重复的表名：
select a.table_name,b.ordinal_position
from information_schema.tables a left outer join
information_schema.key_column_usage b
on a.table_name = b.table_name
您需要一个遍历外键关系的整个依赖树的递归查询。
以下查询针对简单的依赖项执行此操作。它不处理循环外键
with recursive fk_tree as (
-- All tables not referencing anything else
select t.oid as reloid,
t.relname as table_name,
s.nspname as schema_name,
null::text as referenced_table_name,
null::text as referenced_schema_name,
1 as level
from pg_class t
join pg_namespace s on s.oid = t.relnamespace
where relkind = 'r'
and not exists (select *
from pg_constraint
where contype = 'f'
and conrelid = t.oid)
and s.nspname = 'public' -- limit to one schema
union all
select ref.oid,
ref.relname,
rs.nspname,
p.table_name,
p.schema_name,
p.level + 1
from pg_class ref
join pg_namespace rs on rs.oid = ref.relnamespace
join pg_constraint c on c.contype = 'f' and c.conrelid = ref.oid
join fk_tree p on p.reloid = c.confrelid
where ref.oid != p.reloid -- do not enter to tables referencing theirselves.
), all_tables as (
-- this picks the highest level for each table
select schema_name, table_name,
level,
row_number() over (partition by schema_name, table_name order by level desc) as last_table_row
from fk_tree
)
select schema_name, table_name, level
from all_tables at
where last_table_row = 1
order by level;
对于以下表格结构：
create table customer (id integer primary key);
create table product (id integer primary key);
create table manufacturer (id integer primary key);
create table manufactured_by (product_id integer references product, manufacturer_id integer references manufacturer);
create table distributor (id integer primary key);
create table orders (id integer primary key, customer_id integer references customer);
create table order_line (id integer primary key, order_id integer references orders);
create table invoice (id integer, order_id integer references orders);
create table delivery (oder_line_id integer references order_line, distributor_id integer references distributor);
这将返回以下结果：
schema_name | table_name | level
-------------+-----------------+------
public | customer | 1
public | distributor | 1
public | manufacturer | 1
public | product | 1
public | manufactured_by | 2
public | orders | 2
public | order_line | 3
public | invoice | 3
public | delivery | 4
这意味着您需要先插入customer，然后才能插入orders。具有相同级别的表的插入顺序无关紧要。
CTEall_tables的中间步骤需要将每个表只列出一次。否则，表manufactured_by将列出两次：
schema_name | table_name | level
------------+-----------------+------
public | customer | 1
public | product | 1
public | manufacturer | 1
public | distributor | 1
public | manufactured_by | 2
public | manufactured_by | 2
public | orders | 2
public | delivery | 2
public | order_line | 3
public | invoice | 3
public | delivery | 4
这个查询可能需要更多的调整(特别是为了防止依赖树中的循环(，但它应该给你一个开始。
@user330315的原始答案是"几乎在那里"；对我来说。我得到了
错误：递归查询"fk_tree"；第4列具有排序规则"；默认"；在非递归术语中；C"；全面的第6行：null：：text作为referenced_table_name，^提示：使用COLLATE子句设置非递归术语的排序规则。
可能取决于默认排序规则设置。要修复此问题，只需将指定的排序规则添加到null静态列
with recursive fk_tree as (
-- All tables not referencing anything else
select t.oid as reloid,
t.relname as table_name,
s.nspname as schema_name,
null::text COLLATE "en_US" as referenced_table_name, -- <<<-----
null::text COLLATE "en_US" as referenced_schema_name, -- <<<-----
1 as level
. . . . . . . . . .