在PostgreSQL中查找非空列 [英] Finding columns that are NOT NULL in PostgreSQL

问题描述

我为每个表分配了一个可空列计数。简单：

I had an assignment for each table to count nullable columns. Easy:

 SELECT table_name, count(*) FROM INFORMATION_SCHEMA.COLUMNS
 WHERE is_nullable='NO'
 GROUP BY table_name;

现在，我必须对其进行修改以将具有属性的列 NOT NULL。以下代码将执行此操作还是仅检查天气列名称不为空？

Now I have to modify this to count "columns that have property "NOT NULL"". Will the following code do this or will it just check weather column name is not null?

CREATE TEMP TABLE A AS 
SELECT DISTINCT column_name, table_name AS name FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name IS NOT NULL
GROUP BY table_name, column_name;

SELECT name, count(*) FROM A
GROUP BY name;

如果否...有什么建议吗？

If no... Any advices?

推荐答案

否。

此查询

SELECT DISTINCT column_name, table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name IS NOT NULL

将返回在 column_name列中具有值的所有行。

will return all the rows that have a value in the column "column_name".

该表中的所有行在 column_name列中始终具有一个值。

All rows in that table will always have a value in the column "column_name".

需要知道多少列可为空，哪些列不可为空？

Do you just need to know how many columns are nullable and how many are non-nullable?

SELECT is_nullable, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY is_nullable;

按表名计数吗？我想您可以使用它。

Count by table name? I think you can use this.

SELECT table_name, is_nullable, count(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY table_name, is_nullable
ORDER BY table_name, is_nullable;

这篇关于在PostgreSQL中查找非空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！