是否有可能一次性从表中删除所有的NOT NULL约束？
我有一个具有许多NOT NULL约束的大表，并且我正在寻找比单独删除更快的解决方案。
如何一次性从PostgreSQL表中删除所有NOT NULL约束
104
- Stefan
1
尝试查看这些链接是否可以帮助您找到实现此操作的方法。https://dev59.com/VnA75IYBdhLWcg3wS3FA https://dev59.com/l3E85IYBdhLWcg3w8IM4 - Kpt.Khaos
6个回答
161
您可以将它们全部分组到同一个 ALTER 语句中：
alter table tbl alter col1 drop not null,
alter col2 drop not null,
…
如果您想编写一个do block生成所需的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;
请注意，这也会包括与主键相关的字段，因此您需要将其过滤掉。
如果您这样做，请不要忘记在处理列名中可能存在奇怪字符的情况下使用quote_ident()。
- Denis de Bernardy
1
2你的查询很棒。我从未查询过目录。尝试一下给DO块加上会是我的下一步。 - Stefan
24
修改表 table_name 的列 column_name [设置为非空|取消非空]
- jameel
10
如果您想在PostgreSQL中删除所有NOT NULL约束，可以使用以下函数:
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 = $1::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 = $1::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 ' || $1 ||' ALTER COLUMN '||columnName||' DROP NOT NULL';
END LOOP;
RAISE NOTICE 'Done removing the NOT NULL Constraints for TABLE: %', $1;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
请注意主键将被排除。
然后您可以这样调用它：
SELECT dropNull(TABLENAME);
- Paulo Fidalgo
7
前置条件
一些列可能需要通过约束保持NOT NULL。唯一的标准情况是PRIMARY KEY列。您可能还有其他自定义约束。可以通过检查系统目录pg_constraint来完成。请参见下文。
还要注意：下面的代码示例适用于Postgres 15，但是未来版本中对系统目录的更改可能会使其无效（即使不太可能）。
正确的方法
干净的方法只需要常规权限来ALTER表。您可以在DO语句中使用动态SQL自动化实现（实现了Denis已经建议的内容）：
DO
$do$
DECLARE
_tbl regclass := 'tbl_b'; -- your table here, schema-qualify if needed!
BEGIN
RAISE NOTICE '%', ( -- debug mode
-- EXECUTE ( -- uncomment to automate payload
SELECT concat_ws(' '
, 'ALTER TABLE'
, _tbl
, 'ALTER'
, string_agg (quote_ident(attname), ' DROP NOT NULL, ALTER ')
, 'DROP NOT NULL'
)
FROM pg_catalog.pg_attribute a
WHERE attrelid = _tbl
AND attnotnull -- only NOT NULL columns
AND NOT attisdropped -- exclude dropped columns
AND attnum > 0 -- exclude system columns
AND NOT EXISTS ( -- exclude PK columns
SELECT FROM pg_constraint c
WHERE c.conrelid = a.attrelid
AND c.contype = 'p' -- PRIMARY KEY
AND a.attnum = ANY (c.conkey)
)
);
END
$do$;
非常快。在现代版本的Postgres中不会触发表重写。
使用动态命令时要小心，并注意SQL注入。我将其切换到调试模式，因此它只报告DDL代码为NOTICE。看一下吧。
如果您有信心，请注释掉RAISE NOTICE行并取消注释EXECUTE以准备炸弹。
这是从这个更大的答案中分离出来的：
在那里，我从一个使用以下语句创建的表中删除了NOT NULL约束：
CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);
根据文档所述：
非空约束始终会被复制到新表中。
简单粗暴
有一种需要超级用户权限的简单粗暴的方法：
UPDATE pg_attribute
SET attnotnull = FALSE
WHERE attrelid = 'tbl_b'::regclass -- schema-qualify if needed!
AND attnotnull -- only NOT NULL columns
AND NOT attisdropped -- exclude dropped columns
AND attnum > 0 -- exclude system columns
AND NOT EXISTS ( -- exclude PK columns
SELECT FROM pg_constraint c
WHERE c.conrelid = a.attrelid
AND c.contype = 'p' -- PRIMARY KEY
AND a.attnum = ANY (c.conkey)
);
使用快捷方式很诱人。但是如果搞砸了，你可能会破坏你的数据库（集群）。基本规则是：不要直接篡改系统目录。因此不建议这样做。
- Erwin Brandstetter
2
1“DO”块代码段无法工作，因为它还包含了pg拒绝删除的主键。 - user
@user：OP要求“所有NOT NULL约束条件”。为了使答案更具普适性，我更新了以免除PK列。 - Erwin Brandstetter
0
我曾经遇到这样的场景，需要在整个数据库中删除特定名称的每个字段中的
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; $$;
借鉴了其他一些例子，这个更适合我的需求
- Gareth Pursehouse
-4
是的，没错。我也遇到了同样的问题。
为了解决这个问题，我不得不编写一个 C# .net 脚本，遍历所有的 plSql 数据库并删除所有匹配的约束。
如果您想了解如何删除单个约束的具体信息，请参考以下链接。http://www.techonthenet.com/oracle/foreign_keys/drop.php
- Vipul Mishra
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接