如何一次性删除 PostgreSQL 表中的所有 NOT NULL 约束

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..

For, specific info on how to remove single constraints, pl follow the link. http://www.techonthenet.com/oracle/foreign_keys/drop.php

You can group them all in the same alter statement:

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 block to generate the needed sql. For instance, something like:

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.

If you want to drop all NOT NULL constraints in PostreSQL you can use this function:

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;

Please note that the primary keys will be excluded.

Then you can call it using:

SELECT dropNull(TABLENAME);

There is a quick and dirty way with 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 privileges to alter the table: automate it with dynamic SQL in a DO statement (this implements what Denis already suggested):

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
$do$

Still very fast. Execute care with dynamic commands and be wary of SQL injection.

This is a spin-off from this bigger answer:

• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Over there I drop NOT NULL constraints from a table created with:

CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);

Since, per documentation:

Not-null constraints are always copied to the new table.

ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| DROP NOT NULL]

I had a scenario needing to remove the NOT NULL from every field with a certain name across the entire database. Here was my solution. The where clause could be modified to handle whatever search pattern you need.

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