반응형
postgres에서 열거형 값을 삭제하는 방법은 무엇입니까?
postgresql에서 만든 열거형 값을 삭제하려면 어떻게 해야 합니까?
create type admin_level1 as enum('classifier', 'moderator', 'god');
예: 제거하려는 경우moderator명단에서
서류상으로는 아무것도 찾을 수가 없어요.
Postgresql 9.3.4를 사용하고 있습니다.
다음을 사용하여 다른 유형과 마찬가지로 열거형을 삭제(삭제)할 수 있습니다.
DROP TYPE admin_level1;
실제로 열거형에서 개별 값을 제거하는 방법에 대해 질문하는 것이 가능합니까?만약 그렇다면, 당신은 할 수 없습니다.지원되지 않습니다.
비록 ~일지라도
enum유형은 주로 정적 값 집합을 대상으로 하며, 기존 열거형 유형에 새 값을 추가하고 값 이름을 변경할 수 있습니다( 참조).ALTER TYPE) 기존 값은 열거형에서 제거할 수 없으며 열거형을 삭제하고 다시 만들지 않는 한 이러한 값의 정렬 순서를 변경할 수 없습니다.
값을 지정하지 않고 새 형식을 만들고 기존 형식의 모든 기존 사용을 새 형식을 사용하도록 변환한 다음 이전 형식을 삭제해야 합니다.
예.
CREATE TYPE admin_level1 AS ENUM ('classifier', 'moderator');
CREATE TABLE blah (
user_id integer primary key,
power admin_level1 not null
);
INSERT INTO blah(user_id, power) VALUES (1, 'moderator'), (10, 'classifier');
ALTER TYPE admin_level1 ADD VALUE 'god';
INSERT INTO blah(user_id, power) VALUES (42, 'god');
-- .... oops, maybe that was a bad idea
CREATE TYPE admin_level1_new AS ENUM ('classifier', 'moderator');
-- Remove values that won't be compatible with new definition
-- You don't have to delete, you might update instead
DELETE FROM blah WHERE power = 'god';
-- Convert to new type, casting via text representation
ALTER TABLE blah
ALTER COLUMN power TYPE admin_level1_new
USING (power::text::admin_level1_new);
-- and swap the types
DROP TYPE admin_level1;
ALTER TYPE admin_level1_new RENAME TO admin_level1;
여기에 아주 잘 쓰여 있습니다.
http://http.yo1.dog/post-message-values-in-postgresql-안전하고 안전한 방법/
기존 유형의 이름 변경
ALTER TYPE status_enum RENAME TO status_enum_old;
새 유형 생성
CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');
새 유형을 사용하도록 열 업데이트
ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status::text::status_enum;
구형을 제거합니다.
DROP TYPE status_enum_old;
발생 가능한 오류 및 문제 해결:
invalid input value for enum {enum name}: "{some value}"하나 이상의 행에 값이 있습니다("{some value}") 새 유형이 아닙니다.열 유형을 업데이트하려면 먼저 이러한 행을 처리해야 합니다.default for column "{column_name}" cannot be cast automatically to type {enum_name}열의 기본값은 새 유형에 없습니다.열 유형을 업데이트하려면 먼저 열의 기본값을 변경하거나 제거해야 합니다.이 추가에 대해 필립에게 감사드립니다.cannot alter type of a column used by a view or rule열을 사용하는 모든 보기 및 규칙은 ALTER를 실행한 후 다시 생성하기 전에 삭제해야 합니다.자동으로 그렇게 하는 방법이 있습니다.
열거형 항목을 삭제하려면 Postgre의 시스템 테이블에서 작업해야 합니다.SQL.
이 명령을 사용하면 모든 항목 열거 유형을 표시할 수 있습니다.
선택 * pg_enum에서 선택;
그런 다음 검색된 값이 고유한지 확인합니다.rekoru를 제거하는 동안 고유성을 높이려면 'enum label' 외에 'enum typid'도 전달해야 합니다.
이 명령은 열거형의 항목을 제거합니다. 여기서 'unique'는 값입니다.
pg_message에서 삭제합니다. 여기서 en.messagetypeid=124 및 en.messagelabel='message';
참고 제가 설명한 예제는 열거형에 새 값을 추가할 때 사용해야 하지만 데이터베이스 어디에서도 사용하지 않았습니다.
열거값을 수정하려는 사람들에게, 그것을 다시 만드는 것이 실행 가능하고 안전한 유일한 해결책인 것 같습니다.
이는 임시로 열거형 열을 문자열 형식으로 변환하고 열거형을 다시 만든 다음 문자열 열을 다시 열거형으로 다시 변환하는 것으로 구성됩니다.
다음은 예입니다.
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE varchar(255);
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
DROP TYPE your_schema.your_enum_name;
CREATE TYPE your_schema.your_enum_name AS ENUM ('enum1', 'enum2', 'enum3');
ALTER TABLE your_schema.your_table ALTER your_column DROP DEFAULT;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column TYPE your_schema.your_enum_name USING your_enum_name::your_schema.your_column;
ALTER TABLE your_schema.your_table ALTER COLUMN your_column SET DEFAULT('your_default_enum_value');
다음 쿼리를 사용하여 Postgresql 유형에서 ENUM 값 삭제
DELETE FROM pg_enum
WHERE enumlabel = 'moderator'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'admin_level1');
유형 및 가치에 대한 정보만 제공
DELETE FROM pg_enum
WHERE enumlabel = 'ENUM_VALUE'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'ENUM_TYPE')
기존 값을 다른 값으로 변경해야 합니다.새 값을 추가해야 하는 경우 다음을 사용합니다.
ALTER TYPE **ENUM_TYPE** ADD VALUE '**ENUM_VALUE2**';
삭제하기 전에 형식 값을 새 형식 값 또는 기존 값으로 업데이트합니다.
이를 위한 프로그램 방식은 다음과 같습니다.https://stackoverflow.com/a/47305844/629272 에서 제공된 것과 동일한 일반적인 단계가 적절하지만, 이러한 단계는 제 목적(알레믹 다운 마이그레이션 작성)에 맞지 않는 수동적인 것입니다. my_type,my_type_old,그리고.value_to_delete물론, 적절하게 바뀌어야 합니다.
유형 이름을 변경합니다.
ALTER TYPE my_type RENAME TO my_type_old;삭제할 값을 제외하고 이전 유형의 값으로 새 유형을 만듭니다.
DO $$ BEGIN EXECUTE format( 'CREATE TYPE my_type AS ENUM (%s)', ( SELECT string_agg(quote_literal(value), ',') FROM unnest(enum_range(NULL::my_type_old)) value WHERE value <> 'value_to_delete' ) ); END $$;이전 유형을 사용하는 모든 기존 열을 새 열을 사용하도록 변경합니다.
DO $$ DECLARE column_data record; table_name varchar(255); column_name varchar(255); BEGIN FOR column_data IN SELECT cols.table_name, cols.column_name FROM information_schema.columns cols WHERE udt_name = 'my_type_old' LOOP table_name := column_data.table_name; column_name := column_data.column_name; EXECUTE format( ' ALTER TABLE %s ALTER COLUMN %s TYPE my_type USING %s::text::my_type; ', table_name, column_name, column_name ); END LOOP; END $$;이전 유형을 삭제합니다.
DROP TYPE my_type_old;
이 솔루션은 시나리오에 수행할 권한이 없다는 사실에서 시작됩니다.DELETE FROM pg_enum권한 오류가 발생하고 있기 때문입니다.
다른 응답에서 시작하여 열거형에서 단일 값을 제거하는 데 사용할 수 있는 일반 함수를 만들어 업데이트 값이 지정된 값의 사용을 해제할 수 있도록 지원했습니다.
-- https://stackoverflow.com/a/62444685
-- https://stackoverflow.com/a/51073579
create or replace function remove_enum_value(
type_name text, -- Name of the type where you need to remove a value from
value_to_delete text, -- Specific value of the given type you want to remove
value_fallback text, -- Which new value columns will have instead of the value deleted
column_default text -- DEFAULT value for the column after type alteration (DEFAULT need to be disabled before changing type, https://stackoverflow.com/a/41149789)
)
RETURNS VOID AS $body$
declare
-- Used as temporary type
_type_name_tmp text := type_name || '_tmp_' || floor(extract(epoch from now()) * 1000);
-- Used to store statements to execute
_sql text;
-- Used to loop tables and switch type from current to temporary
_column_data record;
_table_name varchar(255);
_column_name varchar(255);
begin
--------------------------------------------------------------------------------------------------------------
-- Check: required inputs
if type_name is null
then
raise exception 'Parameter type_name is null';
end if;
if value_to_delete is null
then
raise exception 'Parameter value_to_delete is null';
end if;
-- Check: type exists
IF not EXISTS (SELECT 1 FROM pg_type WHERE typname = type_name) THEN
raise info 'Type %s does not exists', type_name;
return;
END IF;
-- Check: value to delete exists
if not exists(
select *
FROM pg_enum -- check existing of value to delete
WHERE enumtypid = (select oid from pg_type where typName=cast(type_name as varchar) limit 1) and enumlabel=cast(value_to_delete as varchar)
)
then
raise info 'Value to delete % does not exists in type %s', value_to_delete, type_name;
return;
end if;
-- Check: fallback value is provided and exists
if value_fallback is not null and not exists(
select *
FROM pg_enum -- check existing of value to delete
where
enumtypid = (select oid from pg_type where typName=cast(type_name as varchar) limit 1)
and enumlabel=cast(value_fallback as varchar)
)
then
raise info 'Fallback value % does not exists in type %s', value_fallback, type_name;
return;
end if;
-- Check values are different
if value_fallback = value_to_delete
then
raise info 'Value to delete %s is the same as fallback value %', value_to_delete, value_fallback;
return;
end if;
raise info 'Checks passed, ready to process!';
--------------------------------------------------------------------------------------------------------------
-- Retrieve current values of type
_sql := format('
SELECT string_agg(quote_literal(value), '','')
FROM unnest(enum_range(NULL::%s)) value
WHERE value <> ''%s''
', type_name, value_to_delete);
raise info '%', _sql;
execute _sql into _sql;
-- Create temporary enum
_sql := format(
'CREATE TYPE %s AS ENUM (%s)',
_type_name_tmp,
_sql
);
raise info '%', _sql;
execute _sql;
-- Rename all values from value that need to be deleted to new value (selecting all tables with schemas which has column with enum relation)
for _column_data in (
select
concat(c.table_schema,'.',c.table_name ) as table_name,
c.column_name
FROM information_schema.columns c
where
c.udt_name = cast(type_name as varchar)
and c.table_schema=c.udt_schema
and data_type = 'USER-DEFINED'
)
LOOP
_sql:= format('UPDATE %1$s set %2$s = %3$L where %2$s=%4$L', _column_data.table_name, _column_data.column_name, value_fallback, value_to_delete);
raise info 'Update by looping: %', _sql;
EXECUTE _sql;
END LOOP;
-- Switch type from current to temporary
FOR _column_data in (
SELECT cols.table_name, cols.column_name
FROM information_schema.columns cols
WHERE udt_name = type_name
)
LOOP
_table_name := _column_data.table_name;
_column_name := _column_data.column_name;
_sql := format(
'
ALTER TABLE %s
ALTER COLUMN %s DROP DEFAULT,
ALTER COLUMN %s TYPE %s USING %s::text::%s,
ALTER COLUMN %s SET DEFAULT %s;
',
_table_name,
_column_name,
_column_name, _type_name_tmp, _column_name, _type_name_tmp,
_column_name, (case when column_default is null then null else '''' || column_default || '''::' || _type_name_tmp end)
);
raise info '%', _sql;
execute _sql;
END LOOP;
-- Drop previous type
_sql := format('DROP TYPE %s;', type_name);
raise info '%', _sql;
execute _sql;
-- Rename type to previous name
_sql := format('ALTER TYPE %s RENAME TO %s;', _type_name_tmp, type_name);
raise info '%', _sql;
execute _sql;
END $body$
LANGUAGE plpgsql;
ENUM에서 개별 값을 삭제할 수 없으며, 가능한 유일한 해결책은 DROP하고 필요한 값으로 ENUM을 다시 만드는 것입니다.
v.10. postgres에서도 같은 문제가 있었습니다.삭제하려면 특정 절차가 필요하며, 시퀀스가 올바르지 않으면 테이블이 읽기 위해 잠길 가능성도 있습니다.
삭제하기 편리한 스크립트를 작성했습니다.이미 성능이 여러 배로 입증되었습니다.그러나 이 절차에서는 삭제된 값을 새 값으로 교체해야 합니다(테이블 필드에서 허용하는 경우 NULL일 수 있음).
사용하려면 3개의 값만 입력하면 됩니다.
DO $$
DECLARE
enumTypeName VARCHAR := 'enum_name'; -- VALUE #1, set yor value!
enumOldFieldValue varchar := 'old_enum_value'; -- VALUE #2, enum value which have to be deleted
enumNewFieldValue varchar := null; -- VALUE #3, which new value must be instead of deleted
sql varchar:='';
rec record;
BEGIN
raise info 'Check on old and new enum values.';
IF exists(select * FROM pg_enum -- check existing of OLD enum value
WHERE enumtypid = (select oid from pg_type where typName=cast(enumTypeName as varchar) limit 1) and enumlabel=cast(enumOldFieldValue as varchar))
AND
(exists(select *
FROM pg_enum -- check existing of NEW enum value
WHERE enumtypid = (select oid from pg_type where typName = cast(enumTypeName as varchar) limit 1)
and enumlabel = cast(enumNewFieldValue as varchar))
OR
enumNewFieldValue IS NULL)
THEN
raise info 'Check passed!';
-- selecting all tables with schemas which has column with enum relation
create temporary table tmp_table_names
as SELECT concat(c.table_schema,'.',c.table_name ) as table_name, c.column_name
FROM information_schema.columns c
WHERE c.udt_name = cast(enumTypeName as varchar)
and c.table_schema=c.udt_schema and data_type = 'USER-DEFINED';
-- if we have table(s) that uses such enum
if exists(select * from tmp_table_names)
then
FOR rec in (select table_name, column_name from tmp_table_names) LOOP
sql:= format('UPDATE %1$s set %2$s = %3$L where %2$s=%4$L',rec.table_name, rec.column_name, enumNewFieldValue, enumOldFieldValue);
raise info 'Update by looping: %', sql;
EXECUTE sql;
END LOOP;
end if;
-- just after changing all old values in all tables we can delete old enum value
sql := format('DELETE FROM pg_enum WHERE enumtypid = (select oid from pg_type where typName=%1$L limit 1) and enumlabel=%2$L',enumTypeName,enumOldFieldValue);
raise info 'Delete enum value: %', sql;
EXECUTE sql;
drop table tmp_table_names;
ELSE
raise info 'Old or new enum values is missing.';
end if;
END $$;
- 리스트 항목
데터세덤수있정을크경않우은지도로 수 .--column-inserts한 후 .
언급URL : https://stackoverflow.com/questions/25811017/how-to-delete-an-enum-type-value-in-postgres
반응형
'programing' 카테고리의 다른 글
| 목록의 마지막 항목을 제외한 모든 항목을 순환하는 방법은 무엇입니까? (0) | 2023.05.07 |
|---|---|
| 두 어레이를 "+"(어레이 유니온 연산자)와 병합하는 방법 (0) | 2023.05.07 |
| VB에서 DirectCast()와 CType()의 차이입니다.그물 (0) | 2023.05.07 |
| 실행할 프로젝트를 찾을 수 없습니다.프로젝트가 D:\home\site\wwroot에 있는지 확인합니다. (0) | 2023.05.07 |
| 전화로 확인할 때 확인MongoDB 컬렉션의 인덱스? (0) | 2023.05.07 |