wake-up-neo.net

So löschen Sie alle NOT NULL-Einschränkungen aus einer PostgreSQL-Tabelle auf einmal

Ist es möglich, alle NOT NULL-Einschränkungen auf einmal aus einer Tabelle zu löschen?

Ich habe eine große Tabelle mit vielen NOT NULL-Einschränkungen und suche nach einer Lösung, die schneller ist, als sie separat zu löschen.

sqlpostgresqlconstraintssql-dropnotnull
31
Stefan

Sie können sie alle in derselben alter-Anweisung zusammenfassen:

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

Sie können auch die Liste der relevanten Spalten aus dem Katalog abrufen, wenn Sie einen do-Block schreiben möchten, um die benötigte SQL zu generieren. Zum Beispiel so etwas wie:

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

(Beachten Sie, dass dies auch die primärschlüsselbezogenen Felder einschließt. Daher möchten Sie diese herausfiltern.)

Vergessen Sie in diesem Fall nicht, quote_ident() zu verwenden, wenn Sie sich mit möglicherweise ungewöhnlichen Zeichen in Spaltennamen befassen müssen.

62
Denis de Bernardy

Wenn Sie alle NOT NULL-Einschränkungen in PostreSQL löschen möchten, können Sie diese Funktion verwenden:

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;

Bitte beachten Sie, dass die Primärschlüssel ausgeschlossen werden.

Dann können Sie es aufrufen mit:

SELECT dropNull (TABLENAME);

8
Paulo Fidalgo

ALTER TABLE Tabellenname ALTER SPALTE [SET NOT NULL | DROP NOT NULL]

6
jameel

Es gibt einen schnellen und schmutzigen Weg mit Superuser-Rechten :

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

Die Verknüpfung ist verlockend. Aber wenn Sie das vermasseln, können Sie Ihr System kaputt machen.
Die Grundregel lautet: Manipulieren Sie niemals direkt Systemkataloge.

Die saubere Methode benötigt nur reguläre Berechtigungen , um die Tabelle zu ändern: Automatisieren Sie sie mit dynamischem SQL in einer DO -Anweisung (dies implementiert was Denis schon vorgeschlagen hat ):

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

Immer noch sehr schnell. Führen Sie Vorsicht mit dynamischen Befehlen aus und achten Sie auf SQL-Injection.

Dies ist ein Ableger dieser größeren Antwort:
Generieren Sie DEFAULT-Werte in einem CTE UPSERT mit PostgreSQL 9.

Dort müssen wir NOT NULL-Einschränkungen aus einer Tabelle löschen, die erstellt wurde mit:

CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);

Seit laut Dokumentation :

Nicht-Null-Einschränkungen werden immer in die neue Tabelle kopiert.

4
Erwin Brandstetter

In einem Szenario musste der NOT NULL aus jedem Feld mit einem bestimmten Namen in der gesamten Datenbank entfernt werden. Hier war meine Lösung. Die where-Klausel kann geändert werden, um das Suchmuster zu bearbeiten, das Sie benötigen.

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

einige andere Beispiele huckepack, das funktionierte besser für meine Bedürfnisse

0
Gareth Pursehouse