ALTER TABLE

ALTER TABLE [ ONLY ] nome [ * ]
    ação [, ... ]
ALTER TABLE [ ONLY ] nome [ * ]
    RENAME [ COLUMN ] coluna TO novo_nome_da_coluna
ALTER TABLE nome
    RENAME TO novo_nome

onde ação é uma entre:

    ADD [ COLUMN ] coluna tipo [ restrição_de_coluna [ ... ] ]
    DROP [ COLUMN ] coluna [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] coluna TYPE tipo [ USING expressão ]
    ALTER [ COLUMN ] coluna SET DEFAULT expressão
    ALTER [ COLUMN ] coluna DROP DEFAULT
    ALTER [ COLUMN ] coluna { SET | DROP } NOT NULL
    ALTER [ COLUMN ] coluna SET STATISTICS inteiro
    ALTER [ COLUMN ] coluna SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD restrição_de_tabela
    DROP CONSTRAINT nome_da_restrição [ RESTRICT | CASCADE ]
    CLUSTER ON nome_do_índice
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    OWNER TO novo_dono
    SET TABLESPACE nome_do_espaço_de_tabelas

Descrição

O comando ALTER TABLE altera a definição de uma tabela existente. Existem várias sub-formas:

Todas as ações, exceto RENAME, podem ser combinadas em uma lista de alterações múltiplas a serem aplicadas em paralelo. Por exemplo, é possível adicionar várias colunas e/ou alterar o tipo de várias colunas em um único comando. Esta situação é particularmente útil em tabelas grandes, uma vez que somente é necessário realizar uma passagem pela tabela.

É necessário ser o dono da tabela para executar ALTER TABLE; exceto para ALTER TABLE OWNER, que somente pode ser executado por um superusuário.

Parâmetros

Observações

A palavra chave COLUMN é apenas informativa, podendo ser omitida.

Quando uma coluna é adicionada usando ADD COLUMN, todas as linhas existentes na tabela são inicializadas com o valor padrão da coluna (NULL, se não for especificada a cláusula DEFAULT).

Adicionar uma coluna com um valor padrão não nulo, ou mudar o tipo de uma coluna existente, faz com que toda a tabela seja reescrita. Isto pode levar uma quantidade de tempo significativa no caso de uma tabela grande e, temporariamente, será necessário o dobro do espaço em disco.

A adição das restrições CHECK e NOT NULL obrigam varrer toda a tabela para verificar se as linhas existentes estão em concordância com a restrição.

A razão principal para fornecer a opção de especificar várias alterações em um único comando ALTER TABLE, é que várias varreduras ou reescritas da tabela podem assim ser combinadas em uma única passagem pela tabela.

A forma DROP COLUMN não remove fisicamente a coluna, simplesmente torna a coluna invisível para as operações SQL. As operações subseqüentes de inserção e de atualização na tabela armazenam o valor nulo na coluna. Portanto, remover uma coluna é rápido mas não reduz imediatamente o espaço em disco da tabela, porque o espaço ocupado pela coluna removida não é recuperado. O espaço é recuperado ao longo do tempo, à medida que as linhas existentes são atualizadas.

Algumas vezes é vantajoso o fato de ALTER TYPE requerer a reescrita de toda a tabela, porque o processo de reescrita elimina todo o espaço morto presente na tabela. Por exemplo, para recuperar imediatamente o espaço ocupado por uma coluna removida, a forma mais rápida é

ALTER TABLE tabela ALTER COLUMN qualquer_coluna TYPE qualquer_tipo;

onde qualquer_coluna é uma coluna remanescente na tabela, e qualquer_tipo é o mesmo tipo que a coluna já possui. Este comando resulta em uma modificação da tabela que não é semanticamente visível, mas força uma reescrita eliminando os dados que não são mais úteis.

A opção USING de ALTER TYPE pode, na verdade, especificar qualquer expressão envolvendo os valores antigos da linha; ou seja, pode fazer referência a outras colunas assim como à coluna sendo convertida. Isto permite fazer conversões muito gerais através da sintaxe de ALTER TYPE. Por causa desta flexibilidade, a expressão do USING não é aplicada ao valor padrão da coluna (se houver); o resultado pode não ser uma expressão constante conforme requerido por um valor padrão. Isto significa que quando não existe conversão implícita ou de atribuição do tipo antigo para o novo, ALTER TYPE pode falhar na conversão do valor padrão, mesmo que a cláusula USING seja fornecida. Neste caso, deve ser removido o valor padrão utilizando DROP DEFAULT, executado ALTER TYPE e, então, utilizado SET DEFAULT para adicionar um novo valor padrão adequado. Considerações semelhantes se aplicam a índices e restrições envolvendo a coluna.

Se a tabela possuir tabelas descendentes não será permitido adicionar, mudar o nome ou mudar o tipo de uma coluna na tabela ancestral sem fazer o mesmo nas tabelas descendentes. Ou seja, ALTER TABLE ONLY será rejeitado. Isto garante que as tabelas descendentes sempre possuem colunas correspondendo às tabelas ancestrais.

Uma operação DROP COLUMN recursiva remove a coluna da tabela descendente somente se a tabela descendente não herdar esta coluna de outra tabela ancestral, e nunca tiver possuído uma definição independente para a coluna. O DROP COLUMN não recursivo (ou seja, ALTER TABLE ONLY ... DROP COLUMN) nunca remove qualquer coluna de tabela descendente; em vez disso marca a coluna como definida de forma independente em vez de herdada.

Não é permitido alterar qualquer parte dos catálogos do sistema.

Para obter informações adicionais sobre os parâmetros válidos deve ser consultado o comando CREATE TABLE. O Capítulo 5 possui informações adicionais sobre herança.

Exemplos

Para adicionar uma coluna do tipo varchar a uma tabela:

ALTER TABLE distribuidores ADD COLUMN endereco varchar(30);

Para remover uma coluna da tabela:

ALTER TABLE distribuidores DROP COLUMN endereco RESTRICT;

Para mudar o tipo de duas colunas existentes em uma única operação:

ALTER TABLE distribuidores
    ALTER COLUMN endereco TYPE varchar(80),
    ALTER COLUMN nome TYPE varchar(100);

Para mudar uma coluna inteira contendo carimbo do tempo do UNIX para timestamp with time zone através da cláusula USING:

ALTER TABLE foo
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

Para mudar o nome de uma coluna existente:

ALTER TABLE distribuidores RENAME COLUMN endereco TO cidade;

Para mudar o nome de uma tabela existente:

ALTER TABLE distribuidores RENAME TO fornecedores;

Para adicionar uma restrição de não nulo a uma coluna:

ALTER TABLE distribuidores ALTER COLUMN logradouro SET NOT NULL;

Para remover a restrição de não nulo da coluna:

ALTER TABLE distribuidores ALTER COLUMN logradouro DROP NOT NULL;

Para adicionar uma restrição de verificação à tabela:

ALTER TABLE distribuidores ADD CONSTRAINT chk_cep CHECK (char_length(cod_cep) = 8);

Para remover uma restrição de verificação de uma tabela e de todas as suas descendentes:

ALTER TABLE distribuidores DROP CONSTRAINT chk_cep;

Para adicionar uma restrição de chave estrangeira a uma tabela:

ALTER TABLE distribuidores ADD CONSTRAINT fk_dist FOREIGN KEY (endereco) REFERENCES enderecos (endereco) MATCH FULL;

Para adicionar uma restrição de unicidade (multicoluna) à tabela:

ALTER TABLE distribuidores ADD CONSTRAINT unq_id_dist_cod_cep UNIQUE (id_dist, cod_cep);

Para adicionar uma restrição de chave primária a uma tabela com o nome gerado automaticamente, levando em conta que a tabela pode possuir somente uma única chave primária:

ALTER TABLE distribuidores ADD PRIMARY KEY (id_dist);

Para mover a tabela para outro espaço de tabelas:

ALTER TABLE distribuidores SET TABLESPACE espaco_de_tabelas_rapido;

Compatibilidade

As formas ADD, DROP e SET DEFAULT estão em conformidade com o padrão SQL. As outras formas são extensões do PostgreSQL ao padrão SQL. Também, a capacidade de especificar mais de uma manipulação em um único comando ALTER TABLE é uma extensão.

O comando ALTER TABLE DROP COLUMN pode ser utilizado para remover a única coluna da tabela, produzindo uma tabela com zero coluna. Esta é uma extensão ao padrão SQL, que não permite tabelas sem nenhuma coluna.