Início / dba / Perguntas / 35616
Accepted
GuidoS
Asked: 2013-02-28 12:39:14 +0800 CST

Criar índice se não existir

  • 772

Estou trabalhando em uma função que me permite adicionar um índice se ele não existir. Estou enfrentando o problema que não consigo obter uma lista de índices para comparar. Alguma ideia?

Este é um problema semelhante ao da criação da coluna que é resolvido com este código:
https://stackoverflow.com/a/12603892/368511

postgresql index

2 respostas

  1. Best Answer

    Nomes de índice no PostgreSQL

    • Os nomes de índice são exclusivos em um único esquema de banco de dados.
    • Os nomes de índice não podem ser iguais a qualquer outro índice, tabela (estrangeira), exibição (materializada), sequência ou tipo composto definido pelo usuário no mesmo esquema.
    • Duas tabelas no mesmo esquema não podem ter um índice com o mesmo nome. (Segue logicamente.)

    Se você não se importa com o nome do índice, faça com que o Postgres o nomeie automaticamente:

    CREATE INDEX ON tbl1 (col1);

    é (quase) o mesmo que:

    CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

    Exceto que o Postgres evitará colisões de nomenclatura e escolherá automaticamente o próximo nome livre:

    tbl1_col1_idx 
tbl1_col1_idx2
tbl1_col1_idx3
...

    Apenas tente. Mas, obviamente, você não gostaria de criar vários índices redundantes. Portanto, não seria uma boa ideia criar um novo às cegas.

    Teste de existência

    Postgres 9.5 ou mais recente

    Agora disponível:

    CREATE INDEX IF NOT EXISTS ...

    Também funciona para CREATE INDEX CONCURRENTLY IF NOT EXISTS.

    No entanto, o manual adverte :

    Observe que não há garantia de que o índice existente seja semelhante ao que teria sido criado.

    É uma verificação simples para o nome do objeto. (Aplica-se a variantes para versões mais antigas abaixo também.)
    Para encontrar índices existentes na mesma tabela para a(s) mesma(s) coluna(s):

    SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

    Restrições:

    • Funciona apenas para colunas, não para outras expressões de índice.
    • Também relata índices parciais (com WHEREcláusula) e índices de cobertura (com INCLUDEcláusula).
    • Relata qualquer tipo de índice, não apenas índices de árvore B.

    Estude os resultados (se houver) antes de prosseguir ou refine a consulta de acordo com suas necessidades...

    Leitura adicional:

    Postgres 9.4

    Você pode usar a nova função to_regclass()para verificar sem lançar uma exceção:

    DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

    Retorna NULL se um índice (ou outro objeto) com esse nome não existir. Ver:

    Isso não funciona para CREATE INDEX CONCURRENTLY, pois essa variante não pode ser encapsulada em uma transação externa. Veja o comentário de @Gregory abaixo.

    Postgres 9.3 ou anterior

    Transmita o nome qualificado pelo esquema para regclass:

    SELECT 'myschema.myname'::regclass;

    Se lançar uma exceção, o nome é livre.
    Ou, para testar o mesmo sem lançar uma exceção, use uma DOinstrução:

    DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN
    
        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

    A DOdeclaração foi introduzida com o Postgres 9.0. Nas versões anteriores, você precisa criar uma função para fazer o mesmo.
    Detalhes sobre pg_classno manual .
    Noções básicas sobre índices no manual .

    • 123
    • 13

relate perguntas