PostgreSQL如何查询所有引用指定列作为外键的表与对应列
解决方案
你可以通过关联PostgreSQL系统视图直接匹配外键约束元数据,完全不受列名规则影响,不会误匹配到引用其他表的列,核心查询如下:
SELECT kcu.table_schema AS schema_name, kcu.table_name AS table_name, kcu.column_name AS column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE tc.constraint_type = 'FOREIGN KEY' -- 按需修改被引用的目标表、目标列即可 AND ccu.table_name = 'places' AND ccu.column_name = 'place_id';
整合到批量更新逻辑的完整代码
DO $$ DECLARE rec record; offset_value INT := 100000; -- 自定义ID偏移量 BEGIN -- 先更新主表的主键ID,可根据你的外键校验规则调整执行顺序 EXECUTE format('UPDATE %I SET %I = %I + %s;', 'places', 'place_id', 'place_id', offset_value); -- 批量更新所有关联外键列 FOR rec IN ( SELECT kcu.table_schema AS schema_name, kcu.table_name AS table_name, kcu.column_name AS column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE tc.constraint_type = 'FOREIGN KEY' AND ccu.table_name = 'places' AND ccu.column_name = 'place_id' ) LOOP EXECUTE format( 'UPDATE %I.%I SET %I = %I + %s;', rec.schema_name, rec.table_name, rec.column_name, rec.column_name, offset_value ); END LOOP; END; $$ LANGUAGE plpgsql;
注意事项
- 执行前务必先备份完整数据库,建议先在测试环境验证逻辑正确性
- 执行UPDATE前可以先单独运行SELECT部分,核对返回的表和列是否符合预期,避免误修改
- 如果你的外键约束本身配置了
ON UPDATE CASCADE,可以直接更新主表places的place_id,数据库会自动完成所有关联外键的级联更新,不需要单独写批量更新逻辑
内容的提问来源于stack exchange,提问作者Martial P
相关产品推荐
相关产品推荐

