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.