Crear índice si no existe

Solución:

Nombres de índice en PostgreSQL

• Los nombres de índice son únicos en un solo esquema de base de datos.
• Los nombres de índice no pueden ser los mismos que cualquier otro índice, tabla (externa), vista (materializada), secuencia o tipo compuesto definido por el usuario en el mismo esquema.
• Dos tablas del mismo esquema no pueden tener un índice con el mismo nombre. (Sigue lógicamente.)

Si no le importa el nombre del índice, haga que Postgres lo nombre automáticamente:

CREATE INDEX ON tbl1 (col1);

es (casi) lo mismo que:

CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

Excepto que Postgres evitará una colisión de nombres y elegirá automáticamente el siguiente nombre libre:

tbl1_col1_idx 
tbl1_col1_idx2
tbl1_col1_idx3
...

Solo inténtalo. Pero, obviamente, lo harías no desea crear múltiples índices redundantes. Por lo tanto, no sería una buena idea crear uno nuevo a ciegas.

Prueba de existencia

Postgres 9.5 o más reciente

Ya disponible:

CREATE INDEX IF NOT EXISTS ...

También funciona para CREATE INDEX CONCURRENTLY IF NOT EXISTS.

Sin embargo, el manual advierte:

Tenga en cuenta que no hay garantía de que el índice existente sea similar al que se habría creado.

Es una simple verificación del nombre del objeto. (También se aplica a las variantes de versiones anteriores a continuación).
Para buscar índices existentes en la misma tabla para las mismas columnas:

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
   );

Restricciones:

• Solo funciona para columnas, no para otras expresiones de índice.
• También informa índices parciales (con WHERE cláusula) e índices de cobertura (con INCLUDE cláusula).
• Informa cualquier tipo de índice, no solo índices de árbol B.

Estudie los resultados (si los hay) antes de continuar, o refine la consulta según sus necesidades …

Otras lecturas:

• Encuentre tablas con múltiples índices en la misma columna
• Normalice los subíndices de matriz para una matriz unidimensional para que comiencen con 1

Postgres 9.4

Puedes usar la nueva función to_regclass() para verificar sin lanzar una excepción:

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

END
$$;

Devuelve NULL si no existe un índice (u otro objeto) con ese nombre. Ver:

• Cómo comprobar si existe una tabla en un esquema determinado

Esto no funciona para CREATE INDEX CONCURRENTLY, ya que esa variante no se puede incluir en una transacción externa. Vea el comentario de @Gregory a continuación.

Postgres 9.3 o anterior

Transmita el nombre calificado por esquema a regclass:

SELECT 'myschema.myname'::regclass;

Si lanza una excepción, el nombre es gratis.
O, para probar lo mismo sin lanzar una excepción, use un DO declaración:

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
$$;

los DO La declaración se introdujo con Postgres 9.0. En versiones anteriores, debe crear una función para hacer lo mismo.
Detalles sobre pg_class en el manual.
Conceptos básicos sobre índices en el manual.

Estará disponible en 9.5. Aquí está el compromiso de git real https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947

Discusión sobre pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html

---

---

Utiliza Nuestro Buscador