기존 ENUM 유형에 새 값 추가

기존 ENUM 유형에 새 값 추가

는 다을사용테열있다습니이이블는음하를 있습니다.enum 유형을 . 업데이트를 원합니다.enumtype을 입력하여 추가 가능한 값을 지정합니다.기존 값을 삭제하지 않고 새 값만 추가합니다.이것을 하는 가장 간단한 방법은 무엇입니까?

PostgreSQL 9.1에는 ALTER Enum 유형에 대한 기능이 도입되었습니다.

ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to list
ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value';
ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';

참고: Postgre를 사용하는 경우SQL 9.1 이상에서는 트랜잭션 외부에서 변경할 수 있습니다. 간단한 방법은 이 답변을 참조하십시오.

---

저는 며칠 전에도 같은 문제를 겪었고 이 게시물을 발견했습니다.그래서 내 대답은 해결책을 찾는 사람에게 도움이 될 수 있습니다 :)

변경할 열거형을 사용하는 열이 하나 또는 두 개만 있는 경우 이를 시도할 수 있습니다.또한 새 유형의 값 순서를 변경할 수 있습니다.

-- 1. rename the enum type you want to change
alter type some_enum_type rename to _some_enum_type;
-- 2. create new type
create type some_enum_type as enum ('old', 'values', 'and', 'new', 'ones');
-- 3. rename column(s) which uses our enum type
alter table some_table rename column some_column to _some_column;
-- 4. add new column of new type
alter table some_table add some_column some_enum_type not null default 'new';
-- 5. copy values to the new column
update some_table set some_column = _some_column::text::some_enum_type;
-- 6. remove old column and type
alter table some_table drop column _some_column;
drop type _some_enum_type;

1개 이상의 열이 있을 경우 3-6회 반복해야 합니다.

가능한 해결책은 다음과 같습니다. 전제 조건은 사용된 열거형 값에 충돌이 없다는 것입니다.(예: 열거형 값을 제거할 때는 이 값이 더 이상 사용되지 않는지 확인하십시오.)

-- rename the old enum
alter type my_enum rename to my_enum__;
-- create the new enum
create type my_enum as enum ('value1', 'value2', 'value3');

-- alter all you enum columns
alter table my_table
  alter column my_column type my_enum using my_column::text::my_enum;

-- drop the old enum
drop type my_enum__;

이 방법으로도 열 순서는 변경되지 않습니다.

12 그 이후하는 경우에는 Postgres 12(으)로 .ALTER TYPE ... ADD VALUE거래 내부(해석)

ALTER TYPE ... ADD VALUE( 열거형에 새 값을 추가하는 양식)가 트랜잭션 블록 내에서 실행되면 트랜잭션이 커밋될 때까지 새 값을 사용할 수 없습니다.

따라서 마이그레이션에 해킹이 필요하지 않습니다.

UPD: 여기 예가 있습니다(Nick 덕분에).

ALTER TYPE enum_type ADD VALUE 'new_value';

만약 당신이 상황에 빠진다면 당신은 언제 추가해야 합니다.enum, ALTER TYPE할 문 ㅠㅠㅠㅠㅠㅜㅜㅜㅜㅜERROR: ALTER TYPE ... ADD cannot run inside a transaction block(플라이웨이 이슈 #350 참조) 다음에 이러한 값을 추가할 수 있습니다.pg_enum해결 방법으로 직접(type_egais_units는 대상의 입니다.enum):

INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
    SELECT 'type_egais_units'::regtype::oid, 'NEW_ENUM_VALUE', ( SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype )

@Dariusz 1 보완

Rails 4.2.1의 경우 다음 문서 섹션이 있습니다.

트랜잭션 마이그레이션

데이터베이스 어댑터가 DDL 트랜잭션을 지원하는 경우 모든 마이그레이션이 자동으로 트랜잭션으로 래핑됩니다.그러나 트랜잭션 내에서 실행할 수 없는 쿼리가 있으며 이러한 상황에서는 자동 트랜잭션을 해제할 수 있습니다.

class ChangeEnum < ActiveRecord::Migration
  disable_ddl_transaction!

  def up
    execute "ALTER TYPE model_size ADD VALUE 'new_value'"
  end
end

만일 당신이 Rails를 사용하고 있고 몇 개의 문이 있다면, 당신은 다음과 같이 하나씩 실행해야 할 것입니다.

execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'YYY';"
execute "ALTER TYPE XXX ADD VALUE IF NOT EXISTS 'ZZZ';"

Postgres 9.1 설명서에서:

ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

예:

ALTER TYPE user_status ADD VALUE 'PROVISIONAL' AFTER 'NORMAL'

고지 사항:이 해결책을 시도해본 적이 없어서 안 될 수도 있어요 ;-)

당신은 이것을 봐야 합니다.pg_enum기존 ENUM의 레이블만 변경하려는 경우 단순 UPDATE로 변경할 수 있습니다.

새 ENUM 값 추가하기

• 새 을 저새 값을에삽다에 넣습니다.pg_enum새 값이 마지막 값이어야 하는 경우에는 끝입니다.
• 그렇지 않은 경우(기존 ENUM 값 사이에 새 ENUM 값이 필요함) 테이블의 각 고유 값을 맨 위에서 맨 아래로 업데이트해야 합니다.
• 그런다이변합니다야경에서 .pg_enum반대의 순서로

다음과 같은 레이블 세트가 있습니다.

ENUM ('enum1', 'enum2', 'enum3')

다음과 같은 정보를 얻을 수 있습니다.

ENUM ('enum1', 'enum1b', 'enum2', 'enum3')

그러면:

INSERT INTO pg_enum (OID, 'newenum3');
UPDATE TABLE SET enumvalue TO 'newenum3' WHERE enumvalue='enum3';
UPDATE TABLE SET enumvalue TO 'enum3' WHERE enumvalue='enum2';

그러면:

UPDATE TABLE pg_enum SET name='enum1b' WHERE name='enum2' AND enumtypid=OID;

등등...

댓글을 달 수가 없어서 pg_enum 업데이트는 Postgres 8.4에서 작동한다고만 하겠습니다. 우리의 열거형이 설정되는 방식을 위해, 나는 다음을 통해 기존 열거형에 새로운 값을 추가했습니다.

INSERT INTO pg_enum (enumtypid, enumlabel)
  SELECT typelem, 'NEWENUM' FROM pg_type WHERE
    typname = '_ENUMNAME_WITH_LEADING_UNDERSCORE';

조금 무섭긴 하지만 Postgres가 실제로 데이터를 저장하는 방식을 고려하면 말이 됩니다.

pg_enum 업데이트는 위에서 강조한 중간 열 트릭과 마찬가지로 작동합니다.마법을 사용하여 열의 유형을 직접 변경할 수도 있습니다.

CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');

ALTER TABLE foo ALTER COLUMN bar TYPE varchar;

DROP TYPE test;
CREATE TYPE test as enum('a', 'b', 'c');

ALTER TABLE foo ALTER COLUMN bar TYPE test
USING CASE
WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test
ELSE NULL
END;

해당 열거형을 명시적으로 요구하거나 반환하는 함수가 없는 한 좋습니다.(있는 경우 유형을 삭제하면 pgsql이 불만을 제기합니다.)

또한 PG9.1은 열거형에 적용되는 ALTER TYPE 문을 도입하고 있습니다.

http://developer.postgresql.org/pgdocs/postgres/release-9-1-alpha.html

장소에 할 수 해당위치추수가할없다습니주석을에.ALTER TABLE foo ALTER COLUMN bar TYPE new_enum_type USING bar::text::new_enum_type열에 기본값을 지정하지 못했습니다.해야만 했습니다.

ALTER table ALTER COLUMN bar DROP DEFAULT;

그리고 효과가 있었습니다.

다음은 좀 더 일반적이지만 작업 속도가 빠른 솔루션으로, 유형 자체를 변경하는 것 외에 데이터베이스의 모든 열을 업데이트합니다.이 메서드는 ENUM의 새 버전이 둘 이상의 레이블이 다르거나 원래 레이블 중 일부가 누락된 경우에도 적용할 수 있습니다.는 아래코대습다니었체되가드를 대체합니다.my_schema.my_type AS ENUM ('a', 'b', 'c')와 함께ENUM ('a', 'b', 'd', 'e'):

CREATE OR REPLACE FUNCTION tmp() RETURNS BOOLEAN AS
$BODY$

DECLARE
    item RECORD;

BEGIN

    -- 1. create new type in replacement to my_type
    CREATE TYPE my_schema.my_type_NEW
        AS ENUM ('a', 'b', 'd', 'e');

    -- 2. select all columns in the db that have type my_type
    FOR item IN
        SELECT table_schema, table_name, column_name, udt_schema, udt_name
            FROM information_schema.columns
            WHERE
                udt_schema   = 'my_schema'
            AND udt_name     = 'my_type'
    LOOP
        -- 3. Change the type of every column using my_type to my_type_NEW
        EXECUTE
            ' ALTER TABLE ' || item.table_schema || '.' || item.table_name
         || ' ALTER COLUMN ' || item.column_name
         || ' TYPE my_schema.my_type_NEW'
         || ' USING ' || item.column_name || '::text::my_schema.my_type_NEW;';
    END LOOP;

    -- 4. Delete an old version of the type
    DROP TYPE my_schema.my_type;

    -- 5. Remove _NEW suffix from the new type
    ALTER TYPE my_schema.my_type_NEW
        RENAME TO my_type;

    RETURN true;

END
$BODY$
LANGUAGE 'plpgsql';

SELECT * FROM tmp();
DROP FUNCTION tmp();

레이블 순서가 계속되면 실제 데이터 변경이 발생하지 않기 때문에 전체 프로세스가 상당히 빠르게 실행됩니다.나는 5개의 테이블에 방법을 적용했습니다.my_type각각 50,000-70,000개의 줄이 있고, 전체 과정은 단 10초 만에 끝났습니다.

물론 ENUM의 새 버전에서 누락된 레이블이 데이터의 어딘가에서 사용되는 경우 함수는 예외를 반환하지만, 이러한 상황에서는 사전에 무언가를 수행해야 합니다.

거래 중인 솔루션을 찾는 사람들에게는 다음과 같은 방법이 효과적인 것 같습니다.

대신에ENUM,aDOMAIN형식에 사용되어야 합니다.TEXT일부 주석에서 제안한 대로 값이 지정된 허용 값 목록 내에 있는지 확인하는 제약 조건을 사용합니다.유일한 문제는 도메인이 복합 유형에 의해 사용되는 경우 도메인에 어떠한 제약 조건도 추가할 수 없다는 것입니다(문서에 따르면 이것은 "결국 개선되어야 한다"고만 합니다).그러나 이러한 제한은 다음과 같이 함수를 호출하는 제약 조건을 사용하여 해결할 수 있습니다.

START TRANSACTION;

CREATE FUNCTION test_is_allowed_label(lbl TEXT) RETURNS BOOL AS $function$
    SELECT lbl IN ('one', 'two', 'three');
$function$ LANGUAGE SQL IMMUTABLE;

CREATE DOMAIN test_domain AS TEXT CONSTRAINT val_check CHECK (test_is_allowed_label(value));

CREATE TYPE test_composite AS (num INT, word test_domain);

CREATE TABLE test_table (val test_composite);
INSERT INTO test_table (val) VALUES ((1, 'one')::test_composite), ((3, 'three')::test_composite);
-- INSERT INTO test_table (val) VALUES ((4, 'four')::test_composite); -- restricted by the CHECK constraint

CREATE VIEW test_view AS SELECT * FROM test_table; -- just to show that the views using the type work as expected

CREATE OR REPLACE FUNCTION test_is_allowed_label(lbl TEXT) RETURNS BOOL AS $function$
    SELECT lbl IN ('one', 'two', 'three', 'four');
$function$ LANGUAGE SQL IMMUTABLE;

INSERT INTO test_table (val) VALUES ((4, 'four')::test_composite); -- allowed by the new effective definition of the constraint

SELECT * FROM test_view;

CREATE OR REPLACE FUNCTION test_is_allowed_label(lbl TEXT) RETURNS BOOL AS $function$
    SELECT lbl IN ('one', 'two', 'three');
$function$ LANGUAGE SQL IMMUTABLE;

-- INSERT INTO test_table (val) VALUES ((4, 'four')::test_composite); -- restricted by the CHECK constraint, again

SELECT * FROM test_view; -- note the view lists the restricted value 'four' as no checks are made on existing data

DROP VIEW test_view;
DROP TABLE test_table;
DROP TYPE test_composite;
DROP DOMAIN test_domain;
DROP FUNCTION test_is_allowed_label(TEXT);

COMMIT;

이전에는 승인된 답변과 유사한 솔루션을 사용했지만 뷰 또는 함수 또는 복합 유형(특히 수정된 ENUM을 사용하는 다른 뷰를 사용하는 경우)을 고려하면 좋지 않습니다.이 답변에서 제안된 솔루션은 어떤 조건에서도 작동하는 것으로 보입니다.

유일한 단점은 일부 허용된 값이 제거될 때(특히 이 질문의 경우 허용 가능할 수 있음) 기존 데이터에 대해 검사가 수행되지 않는다는 것입니다.(전화:ALTER DOMAIN test_domain VALIDATE CONSTRAINT val_check안타깝게도 복합 유형에서 사용하는 도메인에 새 제약 조건을 추가하는 것과 동일한 오류가 발생합니다.)

다음과 같은 약간의 수정 사항에 유의하십시오. CHECK (value = ANY(get_allowed_values()))어디서 get_allowed_values() function이 허용된 값 목록을 반환했습니다. 작동하지 않습니다. 매우 이상한 일입니다. 따라서 위에서 제안한 솔루션이 안정적으로 작동하기를 바랍니다(지금까지는 그렇습니다...). (실제로 효과가 있어요 - 제 실수였어요)

와 같이, 위서논의바같이와한에,,ALTER트랜잭션 내부에 명령을 쓸 수 없습니다.입니다.retrieving the typelem from pg_type table그리고.calculating the next enumsortorder number;

다음은 제가 사용하는 코드입니다. (삽입 전에 중복값이 있는지 확인합니다(enumtypeid와 enumlabel name 사이의 제약 조건)

INSERT INTO pg_enum (enumtypid, enumlabel, enumsortorder)
    SELECT typelem,
    'NEW_ENUM_VALUE',
    (SELECT MAX(enumsortorder) + 1 
        FROM pg_enum e
        JOIN pg_type p
        ON p.typelem = e.enumtypid
        WHERE p.typname = '_mytypename'
    )
    FROM pg_type p
    WHERE p.typname = '_mytypename'
    AND NOT EXISTS (
        SELECT * FROM 
        pg_enum e
        JOIN pg_type p
        ON p.typelem = e.enumtypid
        WHERE e.enumlabel = 'NEW_ENUM_VALUE'
        AND p.typname = '_mytypename'
    )

pg_type 테이블에는 형식 이름 앞에 밑줄이 추가됩니다.또한 typname은 where 절에서 모두 소문자여야 합니다.

이제 이것을 db마이그레이션 스크립트에 안전하게 쓸 수 있습니다.

DB::statement("ALTER TABLE users DROP CONSTRAINT    users_user_type_check");
$types = ['old_type1', 'old_type1', 'new_type3'];
$result = join( ', ', array_map(function ($value){
           return sprintf("'%s'::character varying", $value);
       }, $types));
DB::statement("ALTER TABLE users ADD CONSTRAINT users_user_type_check CHECK (user_type::text = ANY    (ARRAY[$result]::text[]))");

Navicat을 사용할 때 유형(보기 -> 기타 -> 유형)으로 이동하여 유형의 설계 보기를 확인한 후 "라벨 추가" 단추를 클릭할 수 있습니다.

다른 옵션이 있는지 모르겠지만 다음을 사용하여 값을 낮출 수 있습니다.

select oid from pg_type where typname = 'fase';'
select * from pg_enum where enumtypid = 24773;'
select * from pg_enum where enumtypid = 24773 and enumsortorder = 6;
delete from pg_enum where enumtypid = 24773 and enumsortorder = 6;

가장 간단한 것은 열거형을 없애는 것입니다.쉽게 수정할 수 없기 때문에 거의 사용하지 않아야 합니다.

언급URL : https://stackoverflow.com/questions/1771543/adding-a-new-value-to-an-existing-enum-type