如何通过一次PostgreSQL 9.1查询将TABLE all column从not null更改为null

Question

我将数据从MySQL数据库迁移到PostgreSQL数据库，并错误地将PostgreSQL数据库中的所有列设置为非空。

在此之后，我面临着插入数据的问题，并且必须手动取消选中not null，那么是否有办法对表中的所有列(除了id(主键) )执行此操作。

我也有这个查询单列，但它也很耗时，

ALTER TABLE <table name> ALTER COLUMN <column name> DROP NOT NULL;

Answer 1

我不认为有这方面的内置功能。但是编写一个函数来做这件事是很容易的。例如：

CREATE OR REPLACE FUNCTION set_nullable(relation TEXT)
RETURNS VOID AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN (SELECT * FROM pg_attribute
                WHERE attnotnull = TRUE AND attrelid=relation::regclass::oid AND attnum > 0 AND attname != 'id')
    LOOP
        EXECUTE format('ALTER TABLE %s ALTER COLUMN %s DROP NOT NULL', relation, rec.attname);
        RAISE NOTICE 'Set %.% nullable', relation, rec.attname;
    END LOOP;
END
$$
LANGUAGE plpgsql;

像这样使用它：

SELECT set_nullable('my_table');

Answer 2

只需使用与当前表相同的结构执行新的CREATE TABLE noNULL，并修改所需的NULLable字段。

然后从旧表插入

INSERT INTO noNULL 
      SELECT *
      FROM oldTable

然后删除oldTable并重命名noNull -> oldTable