Comment supprimer toutes les contraintes NOT NULL d'une table PostgreSQL en une seule go

est-il possible de supprimer toutes les contraintes non nulles d'une table en une seule fois?

j'ai une grande table avec beaucoup de contraintes non nulles et je suis à la recherche d'une solution qui est plus rapide que de les laisser tomber séparément.

30
demandé sur Erwin Brandstetter 2013-11-22 17:49:36

6 réponses

vous pouvez les regrouper tous dans la même déclaration alter:

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

vous pouvez également récupérer la liste des colonnes pertinentes du catalogue, si vous avez envie d'écrire un faire bloc pour générer le sql nécessaire. Par exemple, quelque chose comme:

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

(notez que cela inclura les champs primaires liés aux clés aussi, donc vous voudrez les filtrer.)

si vous faites cela, n'oubliez pas d'utiliser quote_ident() dans le cas où vous avez besoin de traiter avec potentiellement des caractères bizarres dans les noms de colonne.

58
répondu Denis de Bernardy 2013-11-22 14:07:33

si vous voulez supprimer toutes les contraintes NOT NULL dans Potresql vous pouvez utiliser cette fonction:

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 = ::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 = ::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 ' ||  ||' ALTER COLUMN '||columnName||' DROP NOT NULL';        
        END LOOP;
    RAISE NOTICE 'Done removing the NOT NULL Constraints for TABLE: %', ;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Veuillez noter que les clés primaires seront exclus.

alors vous pouvez l'appeler en utilisant:

sélectionner dropNull (TABLENAME);

8
répondu Paulo Fidalgo 2014-04-02 10:55:26

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

6
répondu jameel 2014-06-23 12:16:33

Il y a un rapide et sale avec privilèges de super-utilisateur :

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

Le raccourci est tentant. Mais si vous le visser vous risquez de casser votre système.

La règle de base est: Ne jamais toucher aux catalogues système directement.

Le nettoyer la seule façon régulière privilèges pour modifier la table: l'automatiser avec le SQL dynamique dans un DO énoncé (ce qui implémente ce que Denis a déjà proposé de ):

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

toujours très rapide. Exécutez les soins avec des commandes dynamiques et méfiez-vous de L'injection SQL.

c'est un spin-off de cette réponse plus grande:

générer des valeurs par défaut dans un UPSERT CTE en utilisant PostgreSQL 9.3

là, nous avons la nécessité de supprimer les contraintes non nulles d'un tableau créé avec:

CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);

depuis, par documentation :

Les contraintes

non nulles sont toujours copiées dans la nouvelle table.

4
répondu Erwin Brandstetter 2017-05-23 12:02:12

j'ai eu un scénario nécessitant de supprimer le NOT NULL de chaque champ avec un certain nom dans toute la base de données. C'était ma solution. La clause where peut être modifiée pour gérer n'importe quel motif de recherche dont vous avez besoin.

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

greffé quelques autres exemples, cela a fonctionné mieux pour mes besoins

0
répondu Gareth Pursehouse 2018-08-05 06:13:29

Oui, c'est ça. J'ai eu le même problème..

pour résoudre, j'ai dû écrire un script c# .net qui a traversé toute la base de données plSql et supprimé toutes les contraintes correspondantes..

pour, informations spécifiques sur la façon de supprimer les contraintes simples, pl suivez le lien. http://www.techonthenet.com/oracle/foreign_keys/drop.php

-1
répondu Vipul Mishra 2013-11-22 13:53:02