SQL 如何一次性从 PostgreSQL 表中删除所有 NOT NULL 约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20146475/
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
How to drop all NOT NULL constraints from a PostgreSQL table in one go
提问by Stefan
Is it possible to drop all NOT NULL constraints from a table in one go?
是否可以一次性从表中删除所有 NOT NULL 约束？
I have a big table with a lot of NOT NULL constraints and I'm searching for a solution that is faster than dropping them separately.
我有一个包含很多 NOT NULL 约束的大表，我正在寻找比单独删除它们更快的解决方案。
回答by Denis de Bernardy
You can group them all in the same alter statement:
您可以将它们全部分组在同一个 alter 语句中：
alter table tbl alter col1 drop not null,
alter col2 drop not null,
…
You can also retrieve the list of relevant columns from the catalog, if you feel like writing a do blockto generate the needed sql. For instance, something like:
如果您想编写一个do 块来生成所需的 sql ，您还可以从目录中检索相关列的列表。例如，类似于：
select a.attname
from pg_catalog.pg_attribute a
where attrelid = 'tbl'::regclass
and a.attnum > 0
and not a.attisdropped
and a.attnotnull;
(Note that this will include the primary key-related fields too, so you'll want to filter those out.)
（请注意，这也将包括与主键相关的字段，因此您需要将它们过滤掉。）
If you do this, don't forget to use quote_ident()in the event you ever need to deal with potentially weird characters in column names.
如果您这样做，请不要忘记quote_ident()在您需要处理列名称中潜在的奇怪字符时使用。
回答by jameel
ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| DROP NOT NULL]
ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| 删除非空]
回答by Paulo Fidalgo
If you want to drop all NOT NULLconstraints in PostreSQL you can use this function:
如果要删除NOT NULLPostreSQL 中的所有约束，可以使用此函数：
CREATE OR REPLACE FUNCTION dropNull(varchar) RETURNS integer AS $$
DECLARE
columnName varchar(50);
BEGIN
FOR columnName IN
select a.attname
from pg_catalog.pg_attribute a
where attrelid = ::regclass
and a.attnum > 0
and not a.attisdropped
and a.attnotnull and a.attname not in(
SELECT
pg_attribute.attname
FROM pg_index, pg_class, pg_attribute
WHERE
pg_class.oid = ::regclass AND
indrelid = pg_class.oid AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary)
LOOP
EXECUTE 'ALTER TABLE ' || ||' ALTER COLUMN '||columnName||' DROP NOT NULL';
END LOOP;
RAISE NOTICE 'Done removing the NOT NULL Constraints for TABLE: %', ;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
Please note that the primary keys will be excluded.
请注意，主键将被排除在外。
Then you can call it using:
然后你可以使用以下方法调用它：
SELECT dropNull(TABLENAME);
选择 dropNull(TABLENAME);
回答by Erwin Brandstetter
There is a quick and dirty waywith superuser privileges:
有一种具有超级用户权限的快速而肮脏的方法：
UPDATE pg_attribute
SET attnotnull = FALSE
WHERE attrelid = 'tbl_b'::regclass -- schema-qualify if needed!
AND attnotnull
AND NOT attisdropped
AND attnum > 0;
The shortcut is tempting. But if you screw this up you may end up breaking your system.
The basic rule is: never tamper with system catalogs directly.
捷径很诱人。但是如果你搞砸了，你最终可能会破坏你的系统。
基本规则是：永远不要直接篡改系统目录。
The clean way only needs regular privilegesto alter the table: automate it with dynamic SQL in a DOstatement (this implements what Denis already suggested):
该清洁方式只需要定期的权限更改表：使用动态SQL在其自动化DO声明（这实现什么丹尼斯已经建议）：
DO
$$
BEGIN
EXECUTE (
SELECT 'ALTER TABLE tbl_b ALTER '
|| string_agg (quote_ident(attname), ' DROP NOT NULL, ALTER ')
|| ' DROP NOT NULL'
FROM pg_catalog.pg_attribute
WHERE attrelid = 'tbl_b'::regclass
AND attnotnull
AND NOT attisdropped
AND attnum > 0
);
END
$$
Still very fast. Execute care with dynamic commands and be wary of SQL injection.
还是非常快的。小心执行动态命令并警惕 SQL 注入。
This is a spin-off from this bigger answer:
Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
这是这个更大答案的衍生：
使用 PostgreSQL 9.3 在 CTE UPSERT 中生成默认值
There we have the need to drop NOT NULL constraints from a table created with:
我们需要从创建的表中删除 NOT NULL 约束：
CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);
Since, per documentation:
因为，根据文档：
Not-null constraints are always copied to the new table.
非空约束总是被复制到新表中。
回答by Gareth Pursehouse
I had a scenario needing to remove the NOT NULLfrom every field with a certain name across the entire database. Here was my solution. The whereclause could be modified to handle whatever search pattern you need.
我有一个场景需要NOT NULL从整个数据库中具有特定名称的每个字段中删除。这是我的解决方案。where可以修改该子句以处理您需要的任何搜索模式。
DO $$ DECLARE row record;
BEGIN FOR row IN
(
SELECT
table_schema, table_name, column_name
FROM
information_schema.columns
WHERE
column_name IN ( 'field1', 'field2' )
)
LOOP
EXECUTE
'ALTER TABLE ' || row.table_schema || '.' || row.table_name || ' ALTER '
|| string_agg (quote_ident(row.column_name), ' DROP NOT NULL, ALTER ')
|| ' DROP NOT NULL;';
END LOOP;
END; $$;
piggybacked off some other examples, this worked better for my needs
附带一些其他示例，这更适合我的需求
回答by Vipul Mishra
Yes, it is. I had the same issue..
是的。我遇到过同样的问题..
To resolve, I had to write a C# .net script which traversed all the plSql database and removed all the matching constraints..
为了解决这个问题，我必须编写一个 C# .net 脚本，它遍历所有 plSql 数据库并删除所有匹配的约束..
For, specific info on how to remove single constraints, pl follow the link. http://www.techonthenet.com/oracle/foreign_keys/drop.php
有关如何删除单个约束的具体信息，请点击链接。http://www.techonthenet.com/oracle/foreign_keys/drop.php
