sql - saber - validar si existe una tabla en oracle

Cómo verificar si una tabla existe en un esquema dado

Question

Postgres 8.4 y una base de datos mayor contiene tablas comunes en public esquema public y tablas específicas de la company en el esquema de la company .
company nombres de esquema de la company siempre comienzan con ''company'' y terminan con el número de compañía.
Entonces puede haber esquemas:

public
company1
company2
company3
...
companynn

Una aplicación siempre funciona con una sola compañía.
El search_path se especifica en consecuencia en la cadena de conexión odbc o npgsql, como:

search_path=''company3,public''

¿Cómo verificar si existe una tabla dada en un esquema companyn específico?

select isSpecific(''company3'',''tablenotincompany3schema'')

debería devolver false , y

select isSpecific(''company3'',''tableincompany3schema'')

debería volverse true .

En cualquier caso, la función debería verificar solo el esquema companyn pasado, no otros esquemas.

Si existe una tabla dada tanto en public esquema public como en el pasado, la función debe devolver true .
Debería funcionar para Postgres 8.4 o posterior.

Answer 1

Para PostgreSQL 9.3 o menos ... O a quien le guste todo lo normalizado a texto

Tres sabores de mi antigua biblioteca SwissKnife: relname_exists(anyThing) , relname_normalized(anyThing) y relnamechecked_to_array(anyThing) . Todos los controles de la tabla pg_catalog.pg_class y devuelve tipos de datos universales estándar ( booleano , texto o texto []).

/**
 * From my old SwissKnife Lib to your SwissKnife. License CC0.
 * Check and normalize to array the free-parameter relation-name.
 * Options: (name); (name,schema), ("schema.name"). Ignores schema2 in ("schema.name",schema2).
 */
CREATE FUNCTION relname_to_array(text,text default NULL) RETURNS text[] AS $f$
     SELECT array[n.nspname::text, c.relname::text]
     FROM   pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace,
            regexp_split_to_array($1,''/.'') t(x) -- not work with quoted names
     WHERE  CASE
              WHEN COALESCE(x[2],'''')>'''' THEN n.nspname = x[1]      AND c.relname = x[2]
              WHEN $2 IS NULL THEN           n.nspname = ''public''  AND c.relname = $1
              ELSE                           n.nspname = $2        AND c.relname = $1
            END
$f$ language SQL IMMUTABLE;

CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
  SELECT EXISTS (SELECT relname_to_array($1,$2))
$wrap$ language SQL IMMUTABLE;

CREATE FUNCTION relname_normalized(text,text default NULL,boolean DEFAULT true) RETURNS text AS $wrap$
  SELECT COALESCE(array_to_string(relname_to_array($1,$2), ''.''), CASE WHEN $3 THEN '''' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;

Answer 2

Depende de lo que quieras probar exactamente .

Esquema de información?

Para encontrar "si la tabla existe" ( sin importar quién pregunte ), consultar el esquema de information_schema.tables ( information_schema.tables ) es incorrecto , estrictamente hablando, porque ( por documentación ):

Solo se muestran aquellas tablas y vistas a las que el usuario actual tiene acceso (por ser propietario o tener algún privilegio).

La consulta demostrada por @kong puede devolver FALSE , pero la tabla aún puede existir. Responde la pregunta:

¿Cómo verificar si existe una tabla (o vista) y el usuario actual tiene acceso a ella?

SELECT EXISTS (
   SELECT 1
   FROM   information_schema.tables 
   WHERE  table_schema = ''schema_name''
   AND    table_name = ''table_name''
   );

El esquema de información es principalmente útil para mantenerse portátil en las principales versiones y en diferentes RDBMS. Pero la implementación es lenta, porque Postgres tiene que usar vistas sofisticadas para cumplir con el estándar ( information_schema.tables es un ejemplo bastante simple). Y cierta información (como los OID) se pierde en la traducción de los catálogos del sistema, que en realidad llevan toda la información.

Catálogos del sistema

Tu pregunta fue:

¿Cómo verificar si existe una tabla?

SELECT EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = ''schema_name''
   AND    c.relname = ''table_name''
   AND    c.relkind = ''r''    -- only tables
   );

Utilice los catálogos del sistema pg_class y pg_namespace directamente, que también es considerablemente más rápido. Sin embargo, por documentación en pg_class :

El catálogo pg_class cataloga tablas y casi todo lo demás que tiene columnas o es similar a una tabla. Esto incluye índices (pero vea también pg_index ), secuencias , vistas , vistas materializadas , tipos compuestos y tablas TOAST ;

Para esta pregunta en particular, también puede usar la vista del sistema pg_tables . Un poco más simple y portátil en las principales versiones de Postgres (lo cual no es motivo de preocupación para esta consulta básica):

SELECT EXISTS (
   SELECT 1 
   FROM   pg_tables
   WHERE  schemaname = ''schema_name''
   AND    tablename = ''table_name''
   );

Los identificadores deben ser únicos entre todos los objetos mencionados anteriormente. Si quieres preguntar:

¿Cómo verificar si se toma un nombre para una tabla u objeto similar en un esquema dado?

SELECT EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = ''schema_name''
   AND    c.relname = ''table_name''
   );

• Respuesta relacionada en dba.SE discutiendo "Esquema de información frente a catálogos de sistema"

Alternativa: lanzar a la regclass

SELECT ''schema_name.table_name''::regclass

Esto genera una excepción si la tabla (opcionalmente calificada por esquema) (u otro objeto que ocupa ese nombre) no existe.

Si no califica por esquema el nombre de la tabla, una regclass a la regclass predeterminada de la search_path y devuelve el OID para la primera tabla encontrada, o una excepción si la tabla no está en ninguno de los esquemas enumerados. Tenga en cuenta que los esquemas de sistema pg_catalog y pg_temp (el esquema para objetos temporales de la sesión actual) son automáticamente parte de search_path .

Puede usar eso y atrapar una posible excepción en una función. Ejemplo:

• Compruebe si existe secuencia en Postgres (plpgsql)

Una consulta como la anterior evita posibles excepciones y, por lo tanto, es un poco más rápida.

to_regclass(rel_name) en Postgres 9.4+

Mucho más simple ahora:

SELECT to_regclass(''schema_name.table_name'');

Igual que el elenco, to_regclass(rel_name)

... nulo en lugar de arrojar un error si no se encuentra el nombre

Answer 3

Tal vez use information_schema :

SELECT EXISTS(
    SELECT * 
    FROM information_schema.tables 
    WHERE 
      table_schema = ''company3'' AND 
      table_name = ''tableincompany3schema''
);