ALTER TABLE em massa no PostgreSQL…
Escrito por Guedes em Março 27, 2008
Você já se deparou com a necessidade de fazer uma alteração que afeta vários campos de várias tabelas? Imagine a seguinte situação: você possui vários campos em diversas tabelas que representam valores monetários de 10 casas inteiras e 2 decimais, ou seja, tipo numeric(12,2), e por necessidade do cliente o analista informou que isso precisa ser alterado para 15 casas inteiras e 2 decimais, ou seja, tipo numeric(17,2). E agora?
Bem, nosso amigo elefante que nunca esquece, guarda esses dados com bastante carinho e permite que nós, meros mortais, possamos visualizá-los e tomar decisões com eles já que cada banco de dados possui um esquema contendo os dados sobre os seus dados - os metadados. O nome desse esquema é information_schema e contém views que nos permitem, por exemplo, listar os campos de uma tabela com seus respectivos tipos, essa view é declarada como columns. Então o que tem nela?
SELECT * FROM information_schema.columns;(saída muito grande aqui ...)
Testou? “Coisarada” né? Mas que tal listar as colunas de uma tabela especifica? Suponha que exista uma tabela ‘tb_pessoa’:
SELECT * FROM information_schema.columns WHERE table_name = 'tb_pessoa'; table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing ---------------+--------------+---------------+------------------------+------------------+----------------+-------------+-----------------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+-----------+---------------+--------------+------------+---------------------+----------------+--------------------- esquema | esquema | tb_pessoa | tipo_pessoa | 3 | | NO | integer | | | 32 | 2 | 0 | | | | | | | | | | | | | esquema | pg_catalog | int4 | | | | | 3 | NO esquema | esquema | tb_pessoa | nome | 2 | | NO | character varying | 150 | 1073741824 | | | | | | | | | | | | | | | | esquema | pg_catalog | varchar | | | | | 2 | NO esquema | esquema | tb_pessoa | nome_personalizado | 9 | | YES | character varying | 25 | 1073741824 | | | | | | | | | | | | | | | | esquema | pg_catalog | varchar | | | | | 9 | NO esquema | esquema | tb_pessoa | dt_inclusao | 5 | | NO | date | | | | | | | | | | | | | | | | | | esquema | pg_catalog | date | | | | | 5 | NO esquema | esquema | tb_pessoa | nro_documento | 1 | | NO | numeric | | | 14 | 10 | 0 | | | | | | | | | | | | | esquema | pg_catalog | numeric | | | | | 1 | NO
Melhorou né? Mas que tal agora mostrar só o que interessa? Como por exemplo, o nome ta tabela, o nome da coluna, o tipo da mesma e se ela aceita nulo?
SELECT table_name AS nome_tabela, column_name AS nome_coluna, data_type AS tipo, is_nullable AS aceita_nulo FROM information_schema.columns WHERE table_name = 'tb_pessoa'; nome_tabela | nome_coluna | tipo | aceita_nulo ---------------+------------------------+-----------------------------+------------- tb_pessoa | tipo_pessoa | integer | NO tb_pessoa | nome | character varying | NO tb_pessoa | nome_personalizado | character varying | YES tb_pessoa | dt_inclusao | date | NO tb_pessoa | nro_documento | numeric | NO
Melhor ainda né? Se você analisar a descrição da view columns perceberá que pode melhorar a saida acima. Vejamos:
Visão "information_schema.columns" Coluna | Tipo | Modificadores
--------------------------+------------------------------------+---------------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
column_name | information_schema.sql_identifier |
ordinal_position | information_schema.cardinal_number |
column_default | information_schema.character_data |
is_nullable | information_schema.character_data |
data_type | information_schema.character_data |
character_maximum_length | information_schema.cardinal_number |
character_octet_length | information_schema.cardinal_number |
numeric_precision | information_schema.cardinal_number |
numeric_precision_radix | information_schema.cardinal_number |
numeric_scale | information_schema.cardinal_number |
datetime_precision | information_schema.cardinal_number |
interval_type | information_schema.character_data |
interval_precision | information_schema.character_data |
character_set_catalog | information_schema.sql_identifier |
character_set_schema | information_schema.sql_identifier |
character_set_name | information_schema.sql_identifier |
collation_catalog | information_schema.sql_identifier |
collation_schema | information_schema.sql_identifier |
collation_name | information_schema.sql_identifier |
domain_catalog | information_schema.sql_identifier |
domain_schema | information_schema.sql_identifier |
domain_name | information_schema.sql_identifier |
udt_catalog | information_schema.sql_identifier |
udt_schema | information_schema.sql_identifier |
udt_name | information_schema.sql_identifier |
scope_catalog | information_schema.sql_identifier |
scope_schema | information_schema.sql_identifier |
scope_name | information_schema.sql_identifier |
maximum_cardinality | information_schema.cardinal_number |
dtd_identifier | information_schema.sql_identifier |
is_self_referencing | information_schema.character_data |
SELECT
table_name AS nome_tabela,
column_name AS nome_coluna,
data_type AS tipo,
numeric_precision AS digitos,
numeric_scale AS decimais,
is_nullable AS aceita_nulo
FROM
information_schema.columns
WHERE
data_type = 'numeric';
Agora só falta juntar tudo isso numa panela, cozinhar por alguns minutos e servir o script que irá satisfazer o nosso problema inicial (lembra? mudar de numeric(12,2) para numeric(17,2)):
SELECT 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name || ' TYPE numeric(17,2);' FROM information_schema.columns WHERE data_type = 'numeric' AND numeric_precision = 12 AND numeric_scale = 2; ?column? ----------------------------------------------------------------------------------- ALTER TABLE tb_funcionario ALTER COLUMN remuneracao_basica TYPE numeric(17,2); ALTER TABLE tb_pessoa_juridica ALTER COLUMN valor_receita TYPE numeric(17,2); ALTER TABLE tb_fonte_renda_pf ALTER COLUMN valor_renda TYPE numeric(17,2); ALTER TABLE tb_caixa ALTER COLUMN saldo_dia TYPE numeric(17,2); --- CORTE ---
Pronto, ai está o nosso script! Você precisa apenas executá-lo agora.
DESAFIO: como a saida do comando SQL é grande, redirecione a saida para um arquivo e execute-o via psql.
Bom, é isso “:D
Março 28, 2008 às 9:39 am
Pode me dizer o que o scritp abaixo irá fazer? pois desconheço alteração usando SELECT. Para alterar o typo de uma column usando um alter table é visualmente simples, o problema é fica dando ALTER TABLE para todas as tabelas que tem column numeric (12,2). Me esplica melhor ae. E Parabens pelo post.
SELECT
‘ALTER TABLE ‘ || table_name ||
‘ ALTER COLUMN ‘ || column_name ||
‘ TYPE numeric(17,2);’
FROM
information_schema.columns
WHERE
data_type = ‘numeric’ AND
numeric_precision = 12 AND
numeric_scale = 2;
Março 28, 2008 às 10:15 am
Caro Luiz,
O SELECT que voce cita esta Concatenando ‘ALTER TABLE ‘ mais o nome da tabela, mais ‘ ALTER COLUMN ‘ mais o nome da coluna mais ‘ TYPE numeric(17,2)’ o resultado disso é uma saida que lista todos os comandos formatados, prontos para serem executados, em outras palavras voce pode copiar essa saida e colar num arquivo script.sql e executa-lo. No PostgreSQL ele usa a view colmuns para obter essas informações, em outros bancos voce precisa analisar cada caso, no Oracle por exemplo voce utilizaria a view ALL_COLUMNS, por exemplo..
Você ja tentou fazer um select dessa forma?
SELECT 'Nome da pessoa é: ' || nome_pessoa FROM tb_pessoa;
A idéia é a mesma, mas o pulo do gato é utilizar isso para gerar um script para você, ou seja, uma saída de dados que voce pode colocar num arquivo e executar…
espero que tenha entendido… “:)
Março 28, 2008 às 7:14 pm
Antes de mais nada, parabens pelo blog. Particularmente admiro blogs voltados para o técnico (nós).
Respondendo a dúvida do colega, poderíamos ainda utilizar direcionamentos ou pipes nomeados para executar tudo num só comando, ex.:
$ psql -p5433 postgres -c "`psql -p5433 postgres -At <<_SQL_
SELECT 'ALTER TABLE '||table_name||
' ALTER COLUMN '||column_name||
' TYPE numeric(17,2);'pos
FROM information_schema.columns
WHERE
data_type = 'numeric' AND
numeric_precision = 12 AND
numeric_scale = 2;
_SQL_
`"
-Leo
Março 30, 2008 às 1:42 am
Grande Leo!
Bem lembrado, é uma ótima alternativa essa, unindo o útil ao agradável.
[]s