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:
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:
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:
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:
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:
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:
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:
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
Vejamos um exemplo para o tipo estado_civil:
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:
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:
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:
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:
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
Agora na prática:
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:
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!
Trabalho com Java, se eu mudo o tipo de uma coluna para um tipo enum, eu preciso mudar a manipulação dos dados dessa coluna no java para que eu consiga fazer um crud com a tabela?