tipo - Cómo eliminar todas las restricciones NOT NULL de una tabla PostgreSQL de una sola vez

quitar not null postgresql

Question

¿Es posible eliminar todas las restricciones NOT NULL de una tabla de una sola vez?

Tengo una gran mesa con muchas restricciones NO NULAS y estoy buscando una solución que sea más rápida que eliminarlas por separado.

Answer 1

ALTER TABLE table_name ALTER COLUMN [SET NOT NULL | GOTA NO NULA]

Answer 2

Hay una forma rápida y sucia con privilegios de superusuario :

UPDATE pg_attribute
SET attnotnull = FALSE
WHERE attrelid = ''tbl_b''::regclass -- schema-qualify if needed!
AND attnotnull
AND NOT attisdropped
AND attnum > 0;

El atajo es tentador. Pero si lo arruinas puede terminar rompiendo tu sistema.
La regla básica es: nunca manipular directamente los catálogos del sistema.

La forma limpia solo necesita privilegios regulares para alterar la tabla: automatízalo con SQL dinámico en una declaración DO (esto implementa lo que Denis ya sugirió ):

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

Aún muy rápido. Ejecute la atención con comandos dinámicos y desconfíe de la inyección de SQL.

Esta es una consecuencia de esta gran respuesta:
Genere valores DEFAULT en un UPSTE CTE utilizando PostgreSQL 9.3

Ahí tenemos la necesidad de eliminar restricciones NOT NULL de una tabla creada con:

CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);

Desde, por documentación :

Las restricciones no nulas siempre se copian en la nueva tabla.

Answer 3

Puedes agruparlos a todos en la misma sentencia:

alter table tbl alter col1 drop not null,
alter col2 drop not null,
…

También puede recuperar la lista de columnas relevantes del catálogo si desea escribir un bloque do para generar el sql necesario. Por ejemplo, algo como:

select a.attname
from pg_catalog.pg_attribute a
where attrelid = ''tbl''::regclass
and a.attnum > 0
and not a.attisdropped
and a.attnotnull;

(Tenga en cuenta que esto también incluirá los campos relacionados con la clave principal, así que querrá filtrarlos).

Si haces esto, no te olvides de usar quote_ident() en caso de que necesites lidiar con caracteres potencialmente extraños en los nombres de las columnas.

Answer 4

Sí lo es. Tuve el mismo problema ..

Para resolverlo, tuve que escribir un script de C # .net que atravesaba toda la base de datos de plSql y eliminaba todas las restricciones correspondientes.

Para obtener información específica sobre cómo eliminar restricciones únicas, siga el enlace. http://www.techonthenet.com/oracle/foreign_keys/drop.php

Answer 5

Si desea eliminar todas las restricciones NOT NULL en PostreSQL, puede usar esta función:

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;

Tenga en cuenta que las claves primarias serán excluidas.

Entonces puedes llamarlo usando:

SELECT dropNull (TABLENAME);

Answer 6

Tuve un escenario que necesitaba eliminar el NOT NULL de cada campo con un nombre determinado en toda la base de datos. Aquí estaba mi solución. La cláusula where podría modificarse para manejar cualquier patrón de búsqueda que necesite.

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

Junto a otros ejemplos, esto funcionó mejor para mis necesidades