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;
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$;
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)
);
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; $$;
6条答案
按热度按时间x33g5p2x1#
你可以在同一个alter语句中对它们进行分组:
如果您想编写一个do block来生成所需的sql,您还可以从目录中检索相关列的列表。例如,类似于:
(Note这也将包括与主键相关的字段,因此您需要过滤掉这些字段。)
如果您这样做了,请不要忘记在需要处理列名中可能奇怪的字符时使用
quote_ident()
。brvekthn2#
ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| DROP NOT NULL]
d6kp6zgx3#
如果你想删除PostreSQL中的所有
NOT NULL
约束,你可以使用这个函数:请注意,主键将被排除在外。
然后你可以调用它使用:
SELECT dropNull(TABLENAME);
new9mtju4#
前期准备
某些列可能需要保留
NOT NULL
。唯一的标准情况是PRIMARY KEY
列。您可能有其他自定义约束。可以通过检查系统目录pg_constraint
来完成。见下文。还要注意:下面的代码示例可以工作到Postgres 15,但是在未来版本中对系统目录的更改可能会使其无效(即使不太可能)。
正确方式
clean方式只需要常规权限就可以
ALTER
表。您可以在DO
语句中使用动态SQL进行自动化(实现Denis已经建议的内容):非常快。在现代版本的Postgres中不会触发表重写。
小心执行动态命令,并警惕SQL注入。我将其切换到调试模式,因此它只将DDL代码报告为
NOTICE
。看一下。如果你有信心,注解
RAISE NOTICE
行并取消注解EXECUTE
来准备炸弹。这是一个更大的答案的副产品:
在那里,我从一个用以下命令创建的表中删除
NOT NULL
约束:因为,per documentation:
非空约束始终复制到新表中。
又快又脏
有一种超级用户权限的 * 快捷方式 *:
捷径很诱人。但是搞砸了,你可能会破坏你的数据库(集群)。基本规则是:切勿直接篡改系统目录。所以不推荐。
tpxzln5u5#
我有一个场景需要从整个数据库中具有特定名称的每个字段中删除
NOT NULL
。这是我的解决方案。可以修改where
子句以处理所需的任何搜索模式。piggybacked了一些其他的例子,这更适合我的需要
mwngjboj6#
是的,我也有同样的问题。
为了解决这个问题,我不得不写一个C# .net脚本,它遍历了所有的plSql数据库,并删除了所有匹配的约束。
对于,具体信息如何删除单一的约束,请按照链接。http://www.techonthenet.com/oracle/foreign_keys/drop.php