ALTER TABLE [ ONLY ] nome [ * ] ação [, ... ] ALTER TABLE [ ONLY ] nome [ * ] RENAME [ COLUMN ] coluna TO nova_coluna ALTER TABLE nome RENAME TO novo_nome ALTER TABLE nome SET SCHEMA novo_esquema onde ação é um 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 ] DISABLE TRIGGER [ nome_do_gatilho | ALL | USER ] ENABLE TRIGGER [ nome_do_gatilho | ALL | USER ] CLUSTER ON nome_do_índice SET WITHOUT CLUSTER SET WITHOUT OIDS SET ( parâmetro_de_armazenamento = valor [, ... ] ) RESET ( parâmetro_de_armazenamento [, ... ] ) INHERIT tabela_ancestral NO INHERIT tabela_ancestral OWNER TO novo_dono SET TABLESPACE novo_espaço_de_tabelas
O comando ALTER TABLE altera a definição de uma tabela existente. Existem várias sub-formas:
Esta forma adiciona uma coluna à tabela utilizando a mesma sintaxe do comando CREATE TABLE.
Esta forma remove uma coluna da tabela. Os índices e as restrições da tabela que envolvem a coluna também são automaticamente removidos. É necessário especificar CASCADE se algum objeto fora da tabela depender da coluna como, por exemplo, referências de chaves estrangeiras ou visões.
Esta forma muda o tipo de uma coluna da tabela. Os índices e as restrições de tabela simples que envolvem a coluna são automaticamente convertidos para usar o novo tipo da coluna, através da reanálise da expressão original fornecida. A cláusula opcional USING especifica como computar o novo valor da coluna a partir do antigo; quando omitida, a conversão padrão é a mesma de uma conversão de atribuição do tipo antigo para o novo. A cláusula USING deve ser fornecida quando não há nenhuma conversão implícita ou de atribuição do tipo antigo para o novo.
Estas formas definem ou removem o valor padrão para a coluna. O valor padrão somente é aplicado aos comandos INSERT subseqüentes; as linhas existentes na tabela não são modificadas. Também podem ser criados valores padrão para visões e, neste caso, são inseridos dentro do comando INSERT na visão antes da regra ON INSERT da visão ser aplicada.
Estas formas alteram se a coluna está marcada para aceitar valores nulos ou para rejeitar valores nulos. A forma SET NOT NULL só pode ser utilizada quando não existem valores nulos na coluna.
Esta forma define a quantidade de valores coletados nas estatísticas por coluna para as operações subseqüentes de ANALYZE. A quantidade de valores pode ser definida no intervalo de 0 a 1000; como alternativa, pode ser definida como -1 para voltar a utilizar a quantidade de valores coletados padrão do sistema (default_statistics_target). Para obter informações adicionais sobre a utilização de estatísticas pelo planejador de comandos do PostgreSQL deve ser consultada a Seção 13.2.
Esta forma define o modo de armazenamento da coluna. Controla se a coluna é mantida na mesma tabela ou em uma tabela suplementar, e se os dados devem ser comprimidos ou não. Deve ser utilizado PLAIN para valores de comprimento fixo, como integer, que ficam na mesma tabela não comprimido. MAIN é utilizado para dados que ficam na mesma tabela e são compressíveis. EXTERNAL é utilizado para dados externos não comprimidos, e EXTENDED é utilizado para dados externos comprimidos. EXTENDED é o padrão para a maioria dos tipos de dado que suportam armazenamento diferente de PLAIN. A utilização de EXTERNAL torna as operações de substring em colunas text e bytea mais rápidas, às custas de um espaço para armazenamento maior. Deve ser observado que SET STORAGE, por si só, não muda nada na tabela, apenas define a estratégia a ser seguida durante as próximas atualizações da tabela. Para obter informações adicionais deve ser consultada a Seção 52.2.
Esta forma adiciona uma nova restrição à tabela utilizando a mesma sintaxe do comando CREATE TABLE.
Esta forma remove da tabela a restrição especificada.
Estas formas desativam ou ativam os gatilhos que pertencem à tabela. Um gatilho desativado ainda é conhecido pelo sistema, mas não é executado quando o seu evento gatilho ocorre. Para um gatilho postergado (deferred), o status de ativado é verificado quando o evento ocorre, e não quando a função do gatilho é realmente executada. Pode ser desativado ou ativado um único gatilho especificado pelo seu nome, ou todos os gatilhos da tabela, ou somente os gatilhos de usuário (esta opção exclui os gatilhos utilizados para implementar as restrições de chave estrangeira). Desativar e ativar gatilhos de restrições requer privilégios de superusuário; deve ser feito com cuidado porque, como é óbvio, a integridade da restrição não poderá ser garantida se os gatilhos não forem executados.
Esta forma seleciona o índice padrão para as próximas operações de CLUSTER. Não efetua realmente o reagrupamento da tabela.
Esta forma remove da tabela a informação do índice utilizado mais recentemente pelo comando CLUSTER. Afeta as próximas operações de agrupamento que não especificarem um índice.
Esta forma remove da tabela a coluna de sistema oid. É exatamente equivalente a DROP COLUMN oid RESTRICT, exceto que não reclama se já não houver mais a coluna oid.
Deve ser observado que não existe uma variante de ALTER TABLE que permita restaurar os OIDs para a tabela após estes terem sido removidos.
Esta forma altera um ou mais parâmetros de armazenamento da tabela. Para obter mais informações sobre os parâmetros disponíveis deve ser consultado o comando CREATE TABLE. Deve ser observado que o conteúdo da tabela não é modificado imediatamente por este comando; dependendo do parâmetro pode ser necessário reescrever a tabela para obter os efeitos desejados; isto pode ser feito através do comando CLUSTER, ou de uma das formas de ALTER TABLE que obrigam a reescrita da tabela.
Nota: Enquanto o comando CREATE TABLE permite especificar OIDS na sintaxe WITH (parâmetro_de_armazenamento), o comando ALTER TABLE não trata os OIDS como um parâmetro de armazenamento.
Esta forma redefine um ou mais parâmetros de armazenamento com seu valor padrão. Da mesma forma que no SET, poderá ser necessário reescrever a tabela para atualizar toda a tabela.
Esta forma adiciona a tabela como sendo uma nova descendente da tabela ancestral especificada. Em seguida, as consultas à tabela ancestral irão incluir os registros desta tabela. Para ser adicionada como descendente, a tabela deve possuir todas as colunas que a tabela ancestral contém (mas também pode possuir colunas adicionais). As colunas devem possuir tipos de dado correspondentes e, se houver restrição de NOT NULL na coluna da tabela ancestral, também será necessário existir restrição de NOT NULL na coluna da tabela descendente.
Também devem existir restrições correspondentes na tabela descendente para todas as restrições de CHECK existentes na tabela ancestral. No momento as restrições UNIQUE, PRIMARY KEY e FOREIGN KEY não são consideradas, mas isto poderá mudar no futuro.
Esta forma remove a tabela da lista de descendentes da tabela ancestral especificada. As consultas à tabela ancestral não irão mais incluir os registros desta tabela.
Esta forma torna o usuário especificado o dono da tabela, índice, seqüência ou visão.
Esta forma muda o espaço de tabelas da tabela para o espaço de tabelas especificado, e move os arquivos de dados associados à tabela para o novo espaço de tabelas. Havendo índices na tabela, estes não serão movidos; porém, poderão ser movidos em separado através de comandos SET TABLESPACE adicionais. Consulte também CREATE TABLESPACE.
A forma RENAME muda o nome de uma tabela (ou de um índice, de uma seqüência ou de uma visão), ou o nome de uma coluna da tabela. Não produz efeito sobre os dados armazenados.
Esta forma move a tabela para outro esquema. Os índices, restrições e seqüências associados, pertencentes às colunas da tabela, também são movidos.
Todas as ações, exceto RENAME e SET SCHEMA, 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 será necessário realizar uma passagem pela tabela.
É necessário ser o dono da tabela para executar ALTER TABLE. Para mudar o esquema da tabela também é necessário possuir o privilégio CREATE no novo esquema. Para adicionar a tabela como sendo uma nova descendente da tabela ancestral também é necessário ser o dono da tabela ancestral. Para alterar o dono, também é necessário ser um membro direto ou indireto do novo papel (role) dono, e este papel deve possuir o privilégio CREATE no esquema da tabela (Estas restrições garantem que alterar o dono não faz nada que não poderia ser feito removendo e recriando a tabela. Entretanto, de qualquer forma, um superusuário sempre pode alterar o dono de qualquer tabela).
O nome (opcionalmente qualificado pelo esquema) da tabela existente a ser alterada. Se for especificado ONLY, somente esta tabela será alterada. Se não for especificado ONLY, a tabela e todas as suas tabelas descendentes (se existirem) serão alteradas. Pode ser anexado um * ao nome da tabela para indicar que as tabelas descendentes também devem ser alteradas, mas na versão atual este é comportamento padrão (Nas versões anteriores a 7.1 ONLY era o comportamento padrão. O padrão pode ser alterado mudando o parâmetro de configuração sql_inheritance).
O nome de uma coluna nova ou existente.
O novo nome para uma coluna existente.
O novo nome da tabela.
O tipo de dado da nova coluna, ou o novo tipo de dado de uma coluna existente.
A nova restrição de tabela para a tabela.
O nome da restrição existente a ser removida.
Remove, automaticamente, os objetos que dependem da coluna ou da restrição removida (por exemplo, visões fazendo referência à coluna).
Recusa remover a coluna ou a restrição se existirem objetos que dependem das mesmas. Este é o padrão.
O nome de um único gatilho a ser desativado ou ativado.
Desativa ou ativa todos os gatilhos pertencentes à tabela (Requer privilégio de superusuário se algum dos gatilhos for para uma restrição de chave estrangeira).
Desativa ou ativa todos os gatilhos pertencentes à tabela, exceto os gatilhos para restrição de chave estrangeira.
O nome do índice pelo qual a tabela deve ser marcada para agrupamento.
O nome de um parâmetro de armazenamento da tabela.
O novo valor para o parâmetro de armazenamento da tabela. Deve ser um número ou uma palavra, dependendo do parâmetro.
A tabela ancestral a ser associada ou desassociada a esta tabela.
O nome de usuário do novo dono da tabela.
O nome do espaço de tabelas para o qual a tabela será movida.
O nome do esquema para o qual a tabela será movida.
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 correspondentes às das tabelas ancestrais.
Uma operação DROP COLUMN recursiva somente removerá a coluna da tabela descendente 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.
As ações TRIGGER, CLUSTER, OWNER e TABLESPACE não são recursivas às tabelas descendentes; ou seja, sempre agem como se tivesse sido especificado ONLY. A adição de restrição somente pode ser recursiva para restrições CHECK.
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.
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';
Idêntico ao exemplo acima, mas a coluna possui uma expressão padrão que não é convertida automaticamente para o novo tipo de dado:
ALTER TABLE foo
ALTER COLUMN foo_timestamp DROP DEFAULT,
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
ALTER COLUMN foo_timestamp SET DEFAULT now();
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_end
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;
Para mover a tabela para outro esquema:
ALTER TABLE meu_esquema.distribuidores SET SCHEMA seu_esquema;
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. A capacidade de especificar mais de uma manipulação em um único comando ALTER TABLE também é 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.
