Menu

ALTER TABLE no PostgreSQL: adicionar, renomear e mudar tipo

O ALTER TABLE altera uma tabela que já existe: adicionar, renomear e remover colunas, mudar o tipo de uma coluna com USING, definir valor padrão e NOT NULL e adicionar restrições.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

O ALTER TABLE altera uma tabela que já existe. As formas que você mais vai usar são ADD COLUMN, RENAME COLUMN ... TO, DROP COLUMN e ALTER COLUMN ... TYPE. Várias ações podem ir numa única instrução, separadas por vírgulas.

As linhas existentes recebem 'free' em plan e NULL em signed_up. Desde o PostgreSQL 11, adicionar uma coluna com valor padrão constante não reescreve a tabela, então é rápido mesmo em tabelas grandes. Um valor padrão volátil como clock_timestamp() ainda reescreve todas as linhas.

Renomear uma coluna ou uma tabela

Renomear é instantâneo e mantém os dados, os índices e as restrições. As views continuam funcionando, porque apontam para o id interno da tabela, não para o nome. Nomes escritos como texto em outros lugares não acompanham: corpos de funções PL/pgSQL e consultas da aplicação continuam usando o nome antigo, então procure por ele antes de renomear.

Remover uma coluna

O DROP COLUMN remove a coluna e seus dados. Ele falha se uma view ou uma chave estrangeira depender da coluna. CASCADE remove esses objetos também, e IF EXISTS evita o erro quando a coluna já não existe.

Aqui o PostgreSQL não reescreve a tabela. Ele marca a coluna como removida, e o espaço volta conforme as linhas são atualizadas ou quando a tabela é reescrita.

Mudar o tipo de uma coluna

O ALTER COLUMN ... TYPE converte todos os valores. Quando não existe conversão automática do tipo antigo para o novo, adicione USING com uma expressão que calcula o novo valor:

Sem USING, essa instrução falha com column "price" cannot be cast automatically to type numeric. Se um único valor não puder ser convertido, a instrução inteira falha e a tabela fica como estava.

A maioria das mudanças de tipo reescreve a tabela. Algumas não: aumentar um varchar(n) ou trocar varchar por text.

Definir ou remover valor padrão e NOT NULL

O SET NOT NULL percorre a tabela e falha se alguma linha tiver NULL, então preencha as lacunas antes:

DROP DEFAULT e DROP NOT NULL desfazem isso. Um novo valor padrão vale só para inserções futuras. As linhas existentes mantêm seus valores.

Adicionar uma restrição

ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;

Adicionar um CHECK ou uma chave estrangeira percorre a tabela inteira para validar. Numa tabela grande e movimentada, adicione primeiro com NOT VALID, que verifica só as linhas novas. Depois rode ALTER TABLE orders VALIDATE CONSTRAINT total_positive;. Isso verifica as linhas antigas com um lock mais fraco, que deixa leituras e escritas continuarem. A página de chaves estrangeiras usa o mesmo padrão, e a referência do ALTER TABLE lista cada forma e seu lock.

Perguntas frequentes

Dá para mudar a ordem das colunas no PostgreSQL?

Não. O ALTER TABLE não move colunas, e colunas novas sempre entram no final. Liste as colunas na ordem que você quer no seu SELECT, ou crie uma tabela nova nessa ordem e copie os dados.

O ALTER TABLE trava a tabela no PostgreSQL?

A maioria das formas pega um lock ACCESS EXCLUSIVE, que bloqueia leituras e escritas até a instrução terminar. Mudanças rápidas (renomear, remover coluna, adicionar coluna com valor padrão constante) seguram o lock por pouquíssimo tempo. Uma mudança de tipo que reescreve a tabela segura o lock durante toda a reescrita.

Como adicionar várias colunas de uma vez no PostgreSQL?

Separe as ações com vírgulas numa única instrução: ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;. Todas as ações de um mesmo ALTER TABLE rodam juntas, com um único lock.

Dá para desfazer um ALTER TABLE no PostgreSQL?

Dá. No PostgreSQL o DDL é transacional, então um ALTER TABLE dentro de BEGIN ... ROLLBACK é desfeito por completo.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar