Можно ли удалить все ограничения NOT NULL из таблицы за один раз?
У меня есть большая таблица с множеством ограничений NOT NULL, и я ищу решение, которое будет быстрее, чем их удаление по отдельности.
Можно ли удалить все ограничения NOT NULL из таблицы за один раз?
У меня есть большая таблица с множеством ограничений NOT NULL, и я ищу решение, которое будет быстрее, чем их удаление по отдельности.
Вы можете сгруппировать их все в одном операторе alter:
alter table tbl alter col1 drop not null,
alter col2 drop not null,
…
Вы также можете получить список соответствующих столбцов из каталога, если хотите написать блок do для создания необходимого sql. Например, что-то вроде:
select a.attname
from pg_catalog.pg_attribute a
where attrelid = 'tbl'::regclass
and a.attnum > 0
and not a.attisdropped
and a.attnotnull;
(Обратите внимание, что это также будет включать поля, связанные с первичным ключом, поэтому вы захотите отфильтровать их.)
Если вы это сделаете, не забудьте использовать quote_ident()
на тот случай, если вам когда-нибудь понадобится иметь дело с потенциально странными символами в именах столбцов.
ALTER TABLE имя_таблицы ALTER COLUMN [SET NOT NULL | DROP NOT NULL]
Если вы хотите снять все NOT NULL
ограничения в PostreSQL, вы можете использовать эту функцию:
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;
Обратите внимание, что первичные ключи будут исключены.
Затем вы можете вызвать его, используя:
ВЫБРАТЬ dropNull (НАЗВАНИЕ ТАБЛИЦЫ);
Существует быстрый и грязный способ с привилегиями суперпользователя:
UPDATE pg_attribute
SET attnotnull = FALSE
WHERE attrelid = 'tbl_b'::regclass -- schema-qualify if needed!
AND attnotnull
AND NOT attisdropped
AND attnum > 0;
Ярлык заманчив. Но если вы напортачите, то можете сломать вашу систему.
Основное правило: никогда не вмешивайтесь напрямую в системные каталоги.
Чистый способ требует только обычных привилегий для изменения таблицы: автоматизируйте его с помощью динамического SQL в операторе 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
$$
Все еще очень быстро. Будьте осторожны с динамическими командами и остерегайтесь SQL-инъекций.
Это побочный продукт более крупного ответа:
Создание значений DEFAULT в CTE UPSERT с использованием PostgreSQL 9.3
Здесь нам нужно удалить ограничения NOT NULL из таблицы, созданной с помощью:
CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);
Поскольку согласно документации:
Ненулевые ограничения всегда копируются в новую таблицу.
У меня был сценарий, в котором нужно было удалить NOT NULL
из каждого поля с определенным именем во всей базе данных. Вот мое решение. Предложение where
может быть изменено для обработки любого необходимого вам шаблона поиска.
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; $$;
совмещая некоторые другие примеры, это сработало лучше для моих нужд
Да, это. У меня была такая же проблема..
Чтобы решить эту проблему, мне пришлось написать сценарий C # .net, который прошел через всю базу данных plSql и удалил все соответствующие ограничения.
Для получения конкретной информации о том, как удалить отдельные ограничения, перейдите по ссылке. http://www.techonthenet.com/oracle/foreign_keys/drop.php