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