SQL 在 PostgreSQL 中查找 NOT NULL 的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5345380/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding columns that are NOT NULL in PostgreSQL
提问by Allan David Munja
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;
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?
现在我必须修改它以计算“具有属性“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?
如果没有...有什么建议吗？
回答by Mike Sherrill 'Cat Recall'
No.
不。
This query
这个查询
SELECT DISTINCT column_name, table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name IS NOT NULL
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".
该表中的所有行在“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;
回答by J Ngugi
To get a count of all NOT NULL columns in any table, use:
要获取任何表中所有 NOT NULL 列的计数，请使用：
SELECT count(*)
FROM information_schema.columns
WHERE table_schema = 'table_schema_here'
AND table_name = 'table_name_here'
AND is_nullable = 'YES';
I hope this will help someone.
我希望这会帮助某人。
