Jak usunąć wartość typu wyliczenia w postgres?

Jak usunąć wartość typu wyliczenia, którą utworzyłem w postgresql?

create type admin_level1 as enum('classifier', 'moderator', 'god');

Np. Chcę usunąć moderatorz listy.

Nie mogę znaleźć niczego w dokumentach.

Używam Postgresql 9.3.4.

Usuwasz (upuszczasz) typy wyliczeń jak każdy inny typ, za pomocą DROP TYPE:

DROP TYPE admin_level1;

Czy to możliwe, że faktycznie pytasz, jak usunąć indywidualną wartość z typu wyliczenia ? Jeśli tak, nie możesz. Nie jest obsługiwany :

Chociaż enumtypy są głównie przeznaczone dla statycznych zestawów wartości, istnieje obsługa dodawania nowych wartości do istniejącego typu wyliczenia i zmiany nazw wartości (zobacz ALTER TYPE). Istniejących wartości nie można usunąć z typu wyliczenia ani nie można zmienić kolejności sortowania takich wartości, bez porzucenia i ponownego utworzenia typu wyliczenia.

Musisz utworzyć nowy typ bez wartości, przekonwertować wszystkie istniejące zastosowania starego typu na nowy typ, a następnie usunąć stary typ.

Na przykład

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;

Bardzo dobrze napisane tutaj:

http://blog.yo1.dog/updating-enum-values-in-postgresql-the-safe-and-easy-way/

zmienić nazwę istniejącego typu

ALTER TYPE status_enum RENAME TO status_enum_old;

utwórz nowy typ

CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');

zaktualizuj kolumny, aby używały nowego typu

ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status::text::status_enum;

usuń stary typ

DROP TYPE status_enum_old;

Jeśli chcesz usunąć element typu wyliczenia, musisz operować na tabeli systemowej PostgreSQL.

Za pomocą tego polecenia można wyświetlić wszystkie typy wyliczeń elementów.

SELECT * FROM pg_enum;

Następnie sprawdź, czy wyszukana wartość jest unikalna. Aby zwiększyć wyjątkowość podczas usuwania rekoru, oprócz słowa „enumlabel” należy przekazać „enumtypid”.

To polecenie usuwa wpis w typie wyliczenia, gdzie „unikalny” jest wartością.

DELETE FROM pg_enum pl WHERE pl.enumtypid = 124 AND en.enumlabel = 'unique';

UWAGA Opisany przeze mnie przykład musi być użyty, gdy przez przypadek dodamy nową wartość do typu wyliczenia, a jeszcze nie używaliśmy jej nigdzie w bazie danych.

Dla tych, którzy chcą zmodyfikować wartości wyliczenia, odtworzenie tego wydaje się być jedynym realnym i bezpiecznym rozwiązaniem.

Polega ona na tymczasowej konwersji kolumny wyliczenia do formatu ciągu, odtworzeniu wyliczenia, a następnie ponownej konwersji kolumny ciągu z powrotem do typu wyliczenia.

Oto przykład:

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');

Użyj następującego zapytania, aby usunąć wartość ENUM z typu Postgresql

DELETE FROM pg_enum
WHERE enumlabel = 'moderator'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'admin_level1');

Tylko informacje o typie i wartości

DELETE FROM pg_enum
WHERE enumlabel = 'ENUM_VALUE'
AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = 'ENUM_TYPE')

Należy zmienić istniejące wartości na inne. W tym celu, jeśli chcesz dodać nową wartość, użyj:

ALTER TYPE **ENUM_TYPE** ADD VALUE '**ENUM_VALUE2**'; 

Przed usunięciem zaktualizuj wartość typu do nowej wartości typu lub istniejącej wartości.

Programowy sposób na to jest następujący. Te same ogólne kroki, które podano w https://stackoverflow.com/a/47305844/629272, są odpowiednie, ale są one bardziej ręczne niż sensowne dla moich celów (pisanie alembicznej migracji w dół). my_type, my_type_oldi value_to_deleteoczywiście należy je odpowiednio zmienić.

1. Zmień nazwę swojego typu.

   ALTER TYPE my_type RENAME TO my_type_old;

2. Utwórz nowy typ z wartościami ze starego typu, z wyłączeniem tego, który chcesz usunąć.

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

3. Zmień wszystkie istniejące kolumny, które używają starego typu, aby używały nowego.

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

4. Usuń stary typ.

   DROP TYPE my_type_old;

jeśli twój zbiór danych nie jest tak duży, możesz zrzucić --column-inserts, edytując zrzut za pomocą edytora tekstu, usuń wartość i ponownie zaimportuj zrzut

Miałem ten sam problem w wersji 10. postgres. Usunięcie wymaga pewnych procedur, a jeśli sekwencja nie jest poprawna, istnieje nawet szansa, że ​​tabela zostanie zablokowana do odczytu.

Napisałem wygodny skrypt do usunięcia. Już kilkakrotnie udowodniono swoją wydajność. Jednak ta procedura wymaga zastąpienia usuniętej wartości nową (może mieć wartość NULL, jeśli pozwala na to pole tabeli).

Aby skorzystać, wystarczy wypełnić 3 wartości.

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

1. Element listy

Nie można usunąć pojedynczej wartości z ENUM, jedynym możliwym rozwiązaniem jest DROP i odtworzenie ENUM z potrzebnymi wartościami.