Início / dba / Perguntas / 206992
Accepted
tinlyx
Asked: 2018-05-17 21:36:25 +0800 CST

Verifique se duas tabelas possuem a mesma estrutura (são compatíveis)

  • 772

Eu queria saber se existe uma maneira estabelecida de testar se duas tabelas/relações do PostgreSQL têm a mesma estrutura. Em outras palavras, como testar se eles são compatíveis entre si no sentido de que posso realizar operações de conjunto como UNION ALLe EXCEPTneles?

Pesquisei no DBA.SE e em outros lugares e só consigo encontrar perguntas sobre descobrir se o conteúdo de duas tabelas é diferente (por exemplo, Verificar se duas tabelas têm conteúdo idêntico no PostgreSQL ), ou quando a compatibilidade é conhecida (por exemplo , Comparar duas tabelas com a mesma estrutura, mas com números de membros diferentes ). Mas estou interessado em verificar a compatibilidade da estrutura da tabela .

Estou usando o PostgreSQL 10.3, mas as formas compatíveis com o padrão certamente são preferíveis.

postgresql union

5 respostas

  1. Best Answer

    Tendo esquema de exemplo como:

    create table table1 (
  id integer,
  txt text,
  col1 integer,
  col2 integer);

create table table2 (
  id integer,
  txt text,
  col1 text,
  colx integer);

    e supondo que você não se importe com os nomes das colunas, você pode verificar possíveis conflitos em colunas com união com a consulta:

    (select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table1'
except
select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table2')
union all
(select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table2'
except
select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table1'
)
order by 2;

    O resultado mostrará a posição da coluna ofensiva na estrutura e tipo de dados das tabelas.

    data_type   ordinal_position
integer     3
text    3

    Se você tiver essa lista reduzida, poderá verificar os detalhes no discionário information_schema.columns para verificar os detalhes de cada coluna.

    • 3

  2. Acho que a melhor forma é experimentar e ver.

    select * from (
    (select * from pgbench_accounts limit 0) 
    union all
    (select * from pgbench_history limit 0)
) as foo

    Ou ele lança um erro, ou não.

    • 3

  3. A principal maneira padrão de extrair informações sobre um banco de dados SQL do próprio banco de dados é o INFORMATION_SCHEMAesquema, embora você provavelmente precise escrever essa consulta específica.

    Neste caso information_schema.tablesserá chave (veja https://www.postgresql.org/docs/current/static/infoschema-columns.html para documentação). A versão mais simples será que as tabelas são compatíveis se para cada coluna da tabela 1 houver uma coluna correspondente com a mesma ordinal_positione data_typena tabela 2, e o número de colunas em cada uma for o mesmo (portanto, não há extras na tabela 2) .

    Para ficar mais avançado, você pode ter que começar a ser mais confuso sobre a correspondência de tipos: diferentes tipos de string (char, varchar, text) com diferentes atributos (size, collation) podem ser compatíveis, por exemplo, e alguns bancos de dados até permitem números UNIONe tipos de string convertendo implicitamente o numérico [1] e assim por diante.

    [1] Eu não sou um postgres man, então não posso dizer se este é o caso aqui, mas é fácil de testar. O MS SQL Server tenta ser meio útil, mas força o tipo numérico para SELECT mixedtype = 1 UNION SELECT mixedtype = '2'funcionar, mas SELECT mixedtype = 'a' UNION SELECT mixedtype = 2não apode ser forçado a um número inteiro - então, por segurança, eu diria que os tipos de string e numéricos nunca são compatíveis.

    • 2

  4. Se espera-se que os nomes dos campos sejam os mesmos, uma opção é fazer o script de cada tabela para um arquivo e, em seguida, usar uma ferramenta de comparação de arquivos (como o WinMerge) para comparar os dois scripts lado a lado. Quaisquer diferenças na estrutura serão imediatamente óbvias.

    • 1

  5. Seguindo a resposta aceita - se você quiser saber quais nomes de coluna são um problema, use o seguinte:

    SELECT column_name, data_type, ordinal_position
  FROM information_schema.columns
 WHERE table_schema = 'some_schema'
   AND table_name   = 'table_a'
    
except
     
 SELECT column_name, data_type, ordinal_position
  FROM information_schema.columns
 WHERE table_schema = 'some_schema'
   AND table_name   = 'table_b'

    Se você se importa apenas com as diferenças de nome, use SELECT column_nameapenas e não inclua as outras 2 colunas.

    • 1

relate perguntas