Sintaxe no PostgreSQL: ENUM

Os tipos enumerados ou ENUM, são uma das melhores ideias que o PostgreSQL copiou do MySQL. Sério! Eles foram introduzidos na versão 8.3 do Postgres e são realmente muito úteis. Claro que a implementação no PostgreSQL é bem mais elegantes, pois utiliza o CREATE TYPE e ALTER TYPE como veremos a seguir. Isso nos dá uma implementação mais robusta e flexível para o ENUM. O ENUM é ideal para para colunas que podem receber um conjunto relativamente estável de valores possíveis. Sabe aquelas colunas que você usa com frequência como status, estado civil, sexo, etc. Você geralmente tem 3 opções quando não tem o ENUM como tipo de dado:

  • Cria uma tabela auxiliar com um ID e o nome da característica em questão:
SQL
CREATE TABLE estado_civil (
     id      SERIAL PRIMARY KEY,
     nome    VARCHAR(15) NOT NULL UNIQUE ); 

INSERT INTO estado_civil(nome) VALUES ('Solteiro'); 
INSERT INTO estado_civil(nome) VALUES ('Casado'); 
INSERT INTO estado_civil(nome) VALUES ('Divorciado'); 
INSERT INTO estado_civil(nome) VALUES ('Viúvo');
  • Criar uma coluna com os mesmos valores possíveis:
SQL
CREATE TABLE pessoa(
     id      SERIAL PRIMARY KEY,
     nome    VARCHAR(50) NOT NULL UNIQUE,
     estado_civil VARCHAR(15),
     CONSTRAINT estado_civil_check CHECK (
         estado_civil IN ('Solteiro', 'Casado', 'Divorciado', 'Viúvo')
         ) 
 );

  • Criar uma coluna com códigos que representem os valores possíveis:
SQL
CREATE TABLE pessoa (
     id      SERIAL PRIMARY KEY,
     nome    VARCHAR(50) NOT NULL UNIQUE,
     estado_civil CHAR(1),
     CONSTRAINT estado_civil_check CHECK (estado_civil IN ('S', 'C', 'D', 'V')
     ) 
); 
COMMENT ON COLUMN pessoa.estado_civil IS 'S = Solteiro, C = Casado, D = Divorciado, V = Viúvo';

A primeira opção é a que apresenta a melhor modelagem. Permite que você adicione novos tipos de estado civil sem ter que mexer na tabela. Se você tiver varias tabelas com uma FK para a tabela estado_civil, alterando os seus dados, você altera para todas as tabelas relacionadas a identidade do campo estado civil. A única coisa chata é ter que criar uma tabela nova só para isso, e ter que fazer mais um JOIN toda vez que quiser resgatar os nomes dos estados civis.

A segunda opção deverá consumir muito espaço em disco desnecessariamente, uma vez que vai armazenar a palavra inteira e repeti-la varias vezes. Não é uma opção muito elegante.

A terceira opção, não tem o nome do estado civil explícito no valor do campo. Você vai ter que traduzir ‘S’ para ‘Solteiro’ no código da sua aplicação. Isso é uma operação bastante indesejada, pois algo que deveria ser dinâmico, se torna hard coded, com uma manutenção ruim para fazer.

Criando seu ENUM

Vejamos como ficaria o mesmo problema utilizando ENUM:

SQL
CREATE TYPE estado_civil AS ENUM('Solteiro', 'Casado', 'Divorciado', 'Viúvo');

CREATE TABLE pessoa (
    id      SERIAL PRIMARY KEY,
    nome    VARCHAR(50) NOT NULL UNIQUE,
    estado_civil_pessoa estado_civil
);

Veja que você criou um tipo de dados chamado estado_civil e utilizou ele na tabela pessoa. É quase como criar uma tabela auxiliar. Mas não existe um ID no meio. Quando você for fazer um INSERT na tabela pessoa, você tem que utilizar os nomes completos de cada tipo:

SQL
teste=# INSERT INTO pessoa (nome, estado_civil_pessoa) VALUES ('Paulo', 'Solteiro');
INSERT 0 1
teste=# INSERT INTO pessoa (nome, estado_civil_pessoa) VALUES ('João', 'Casado');
INSERT 0 1
teste=# INSERT INTO pessoa (nome, estado_civil_pessoa) VALUES ('Joaquim', 'Enrolado');
ERROR:  invalid input value for enum estado_civil: "Enrolado"
LINE 1: ...oa (nome, estado_civil_pessoa) VALUES ('Joaquim', 'Enrolado'...

Veja que você não precisa adicionar uma constraint para limitar os valores possíveis. Se você tentar inserir um valor inválido, o Postgres vai lhe avisar que aquele valor não é aceito, pois não faz parte do tipo de dados estado_civil. No entanto, você pode querer alterar o tipo de dados e incluir um novo elemento:

SQL
teste=# ALTER TYPE estado_civil ADD VALUE 'Enrolado';
ALTER TYPE
teste=# INSERT INTO pessoa (nome, estado_civil_pessoa) VALUES ('Joaquim', 'Enrolado');
INSERT 0 1

Renomeando

Por fim, você ainda pode alterar o nome de um dos elementos (ou rótulos ou em inglês, labels). A sintaxe é a seguinte:

SQL
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value

Vejamos um exemplo para o tipo estado_civil:

SQL
teste=# SELECT * FROM pessoa WHERE nome = 'Joaquim';
 id |  nome   | estado_civil_pessoa
----+---------+---------------------
  3 | Joaquim | Enrolado
(1 row)

teste=# ALTER TYPE estado_civil RENAME VALUE 'Enrolado' TO 'Amasiado';
ALTER TYPE
teste=# SELECT * FROM pessoa WHERE nome = 'Joaquim';
 id |  nome   | estado_civil_pessoa
----+---------+---------------------
  3 | Joaquim | Amasiado
(1 row)

Ordenação

Se você quiser ordenar as linhas de uma tabela com base em um campo ENUM a ordenação não será feita com base nos valores do tipo estado_civil e sim pela ordem em que eles foram criados no seu tipo:

SQL
teste=# SELECT * FROM pessoa ORDER BY estado_civil_pessoa;
 id |  nome   | estado_civil_pessoa
----+---------+---------------------
  1 | Paulo   | Solteiro
  2 | João    | Casado
  3 | Joaquim | Amasiado
(3 rows)

Se você quiser consultar como estão os elementos do seu tipo ENUM, você pode consultar o catálogo do Postgres com a seguinte consulta:

SQL
SELECT 
    typname         AS nome_tipo,
    typlen          AS tamanho_elemento, 
    enumsortorder   AS ordem,
    enumlabel       AS label
FROM 
    pg_type t 
    JOIN pg_enum e ON t.oid = e.enumtypid 
WHERE typname = 'estado_civil';

  nome_tipo   | tamanho_elemento | ordem |   label
--------------+------------------+-------+------------
 estado_civil |                4 |     1 | Solteiro
 estado_civil |                4 |     2 | Casado
 estado_civil |                4 |     3 | Divorciado
 estado_civil |                4 |     4 | Viúvo
 estado_civil |                4 |     5 | Amasiado
(5 rows)

Veja que todos elementos são armazenados com apenas 4 bytes enquanto o rótulo em si, pode ter até 63 bytes. Na tabela pessoa, independente do tamanho do rótulo serão armazenados apenas 4 bytes, o mesmo tamanho de um campo INTEGER.

Você também pode utilizar um atalho no psql:

SQL
teste=# \dT+ estado_civil
                                           List of data types
 Schema |     Name     | Internal name | Size |  Elements  |  Owner   | Access privileges | Description
--------+--------------+---------------+------+------------+----------+-------------------+------
 public | estado_civil | estado_civil  | 4    | Solteiro  +| postgres |                   |
        |              |               |      | Casado    +|          |                   |
        |              |               |      | Divorciado+|          |                   |
        |              |               |      | Viúvo     +|          |                   |
        |              |               |      | Amasiado   |          |                   |
(1 row)

Você pode ainda adicionar novos elementos em uma posição específica no seu ENUM. Vejamos a sintaxe para isso:

SQL
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]

Agora na prática:

SQL
teste=# ALTER TYPE estado_civil ADD VALUE 'Namorando' AFTER 'Solteiro';
ALTER TYPE
teste=# \dT+ estado_civil
                                           List of data types
 Schema |     Name     | Internal name | Size |  Elements  |  Owner   | Access privileges | Description
--------+--------------+---------------+------+------------+----------+-------------------+-------------
 public | estado_civil | estado_civil  | 4    | Solteiro  +| postgres |                   |
        |              |               |      | Namorando +|          |                   |
        |              |               |      | Casado    +|          |                   |
        |              |               |      | Divorciado+|          |                   |
        |              |               |      | Viúvo     +|          |                   |
        |              |               |      | Amasiado   |          |                   |
(1 row)

Na verdade a sintaxe é um pouco mais elegante, você pode A sintaxe completa para alterar um tipo ENUM

Restrições

  • O tamanho do rótulo ou label, é de no máximo 63 bytes, que é o valor da constante NAMEDATALEN definida no ato de compilação do PostgreSQL. Ou seja, você pode alterar este valor, mas você terá que trabalhar com uma versão compilada por você mesmo do Postgres para isso. Em geral 63 bytes costuma ser mais que suficiente;
  • Você não pode apagar elementos já existentes no seu ENUM nem trocar eles de lugar depois que já foram inseridos;
  • Você não pode rodar um ALTER TYPE … ADD VALUE dentro de uma transação (entre um BEGIN e um COMMIT ou ROLLBACK);
  • Se você tentar dar um DROP no seu tipo de dados ENUM e ele já estiver sendo utilizado em outras tabelas, vai ter que utilizar um CASCADE, o que vai excluir as colunas que utilizam o ENUM nestas tabelas:
SQL
teste=# DROP TYPE estado_civil ; 
ERROR:  cannot drop type estado_civil because other objects depend on it 
DETAIL:  table pessoa column estado_civil_pessoa depends on type estado_civil 
HINT:  Use DROP ... CASCADE to drop the dependent objects too. 

teste=# DROP TYPE estado_civil CASCADE; 
NOTICE:  drop cascades to table pessoa column estado_civil_pessoa DROP TYPE 
teste=# \d pessoa
                                    Table "public.pessoa"
  Column |         Type          | Collation | Nullable |              Default
 --------+-----------------------+-----------+----------+------------------------------------
  id     | integer               |           | not null | nextval('pessoa_id_seq'::regclass)
  nome   | character varying(50) |           | not null | 
Indexes:     
"pessoa_pkey" PRIMARY KEY, btree (id)     
"pessoa_nome_key" UNIQUE CONSTRAINT, btree (nome) 

Conclusão

Podemos dizer que o uso do ENUM:

  • Tem uma sintaxe limpa e simples de utilizar;
  • Torna a sua vida mais simples, pois você declara o tipo ENUM uma vez e pode utilizar em varias tabelas;
  • Torna suas consultas mais simples do que utilizar uma tabela auxiliar, pois você não precisa fazer outro JOIN;
  • É eficiente em termos de armazenamento, pois ocupa pouco espaço em disco;
  • Não exige a utilização de códigos que precisam ser convertidos na aplicação;
  • Tem uma ordenação própria que leva em conta a ordem dos valores em que são adicionados no tipo e não a ordenação alfabética dos seus labels;
  • Tem uma boa flexibilidade para adicionar novos valores em uma ordem específica;
  • Não permite remover labels sem ter que destruir e recriar o ENUM.
  • Devem ser evitados quando a lista de valores possíveis for muito dinâmica. Neste caso uma tabela auxiliar deve ser mais adequada;

Resumindo: não serve para tudo, mas no lugar certo, é uma mão na roda!

Compartilhe

Você pode gostar

Sobre minha saída da Timbira

Há 14 anos, durante o PGConf.Brasil 2009, lá na UNICAMP em Campinas/SP, 4 pessoas se reuniram e idealizaram a criação da primeira empresa dedicada exclusivamente

Split brain

Já tem algum tempo que eu pensava em fazer isso e chegou a hora. Este blog vai se dividir em 2 partes a partir de

plugins premium WordPress