如何在Postgres中通过系统表查询视图指定列是否允许为空

可以直接通过查询Postgres内置的系统目录/信息视图实现该需求，无需额外依赖。

实现原理 #

• Postgres的information_schema.columns信息视图存储了所有表、视图的列的元数据信息，其中is_nullable字段直接标记了对应列是否允许为空
• 如需严格限定仅查询视图（排除普通表、物化视图等对象），可以关联pg_catalog.pg_class系统表，通过relkind = 'v'的条件过滤出视图对象

基础查询语句 #

直接使用information_schema.columns查询，适合确认schema下无同名视图的场景：

SELECT is_nullable
FROM information_schema.columns
WHERE 
  table_schema = 'public' -- 替换为视图实际所属的schema，默认是public
  AND table_name = 'your_view_name' -- 传入视图名称参数
  AND column_name = 'your_column_name'; -- 传入目标列名参数

结果说明 #

• 返回YES：对应列允许为空
• 返回NO：对应列不允许为空
• 无返回结果：查询的视图/列不存在

严格限定视图的查询语句 #

避免误查同名普通表，增加视图类型校验：

SELECT 
  c.is_nullable,
  n.nspname AS schema_name,
  t.relname AS view_name
FROM information_schema.columns c
INNER JOIN pg_catalog.pg_class t 
  ON c.table_name = t.relname
INNER JOIN pg_catalog.pg_namespace n 
  ON t.relnamespace = n.oid 
  AND c.table_schema = n.nspname
WHERE 
  n.nspname = 'public' -- 视图所属schema
  AND t.relname = 'your_view_name' -- 视图名称参数
  AND c.column_name = 'your_column_name' -- 列名参数
  AND t.relkind = 'v'; -- 仅查询普通视图，如需包含物化视图可改为 'm'

封装为函数复用示例 #

如果需要频繁调用，可以直接封装为SQL函数，传入参数即可直接返回布尔结果：

CREATE OR REPLACE FUNCTION check_view_column_nullable(
  p_view_name text, 
  p_column_name text, 
  p_schema text DEFAULT 'public'
) RETURNS boolean
LANGUAGE sql STABLE
AS $$
SELECT CASE WHEN c.is_nullable = 'YES' THEN true ELSE false END
FROM information_schema.columns c
INNER JOIN pg_catalog.pg_class t 
  ON c.table_name = t.relname 
  AND t.relkind = 'v'
INNER JOIN pg_catalog.pg_namespace n 
  ON t.relnamespace = n.oid 
  AND c.table_schema = n.nspname
WHERE 
  n.nspname = p_schema 
  AND t.relname = p_view_name 
  AND c.column_name = p_column_name;
$$;

函数调用示例 #

-- 查询public schema下view1视图的col1列是否允许为空
SELECT check_view_column_nullable('view1', 'col1');
-- 查询自定义schema test下view2视图的col2列是否允许为空
SELECT check_view_column_nullable('view2', 'col2', 'test');

注意事项 #

• Postgres默认会将未加双引号的标识符转为小写存储，如果你创建视图/列时使用了双引号声明大写名称，传参时需要严格匹配大小写
• 如果查询无结果或函数返回null，请校验传入的schema、视图名、列名是否正确，或者对应视图是否存在该列

内容的提问来源于stack exchange，提问作者developer