Comment vérifier si une table existe dans un schéma donné

Postgres 8.4 et une plus grande base de données contient les tables en public schéma de société et de tables spécifiques dans company schéma.

company les noms de schéma commencent toujours par 'company' et se terminent par le numéro d'entreprise.

Il peut donc y avoir des schémas:

public
company1
company2
company3
...
companynn

une application fonctionne toujours avec une seule entreprise.

Le search_path est spécifié en conséquence dans odbc ou Npgsql chaîne de connexion, comme:

search_path='company3,public'

Comment vérifier si une table donnée existe dans un schéma companyn spécifié?

select isSpecific('company3','tablenotincompany3schema')

doit renvoyer false , et

select isSpecific('company3','tableincompany3schema')

doit renvoyer true .

dans tous les cas la fonction devrait vérifier seulement companyn schéma passé, pas d'autres schémas.

si un tableau donné existe à la fois dans public et passé schéma, la fonction doit retourner true .

Il devrait fonctionner pour Postgres 8.4 ou plus tard.

100
demandé sur Erwin Brandstetter 2013-12-14 15:07:45

3 réponses

Cela dépend de ce que vous voulez tester exactement .

schéma D'Information?

pour trouver "si la table existe" ( peu importe qui demande ), interroger le schéma d'information ( information_schema.tables ) est incorrect , à proprement parler, parce que ( par documentation ):

Seulement ces tableaux et vues sont montrés que l'utilisateur courant a accès (par le propriétaire ou d'avoir certains privilèges).

La requête démontré par @kong peut renvoyer FALSE , mais la table peut encore exister. Il répond à la question:

Comment vérifier si une table (ou une vue) existe, et si l'utilisateur courant y a accès?

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

le schéma d'information est principalement utile pour rester portable à travers les principales versions et les différents RDBM. Mais la mise en œuvre est lente, car Postgres doit utiliser des vues sophistiquées pour se conformer à la norme ( information_schema.tables est un exemple assez simple). Et certaines informations (comme OIDs) se perd dans la traduction des catalogues système - qui réellement transporter toutes les informations.

catalogues système

votre question était:

Comment vérifier si une table existe?

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

utilisez les catalogues système pg_class et pg_namespace directement, ce qui est aussi considérablement plus rapide. Toutefois, par documentation sur pg_class :

Le catalogue pg_class catalogues de tableaux et la plupart de tout ce autre chose qui a colonne ou est similaire à un tableau. Cela inclut indexes (mais voir aussi pg_index ), séquences , points de vue , vues matérialisées , composite types , et TOAST tables ;

Pour cette question, vous pouvez également utiliser la fonction système pg_tables . Un peu plus simple et plus portable à travers les principales versions de Postgres (ce qui n'est pas un problème pour cette requête de base):

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

les identificateurs doivent être uniques parmi tous les objets mentionnés ci-dessus. Si vous voulez demander:

Comment vérifier si un nom pour une table ou un objet similaire dans un schéma donné est pris?

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

Alternative: regclass

SELECT 'schema_name.table_name'::regclass

ce soulève une exception si la table (qualifiée de schéma optionnel) (ou un autre objet occupant ce nom) il n'existe pas.

si vous ne qualifiez pas le nom de la table, un cast à regclass par défaut à la search_path et renvoie l'OID pour la première table trouvée - ou une exception si la table n'est dans aucun des schémas listés. Notez que les schémas système pg_catalog et pg_temp (le schéma pour les objets temporaires de la session en cours) font automatiquement partie du search_path .

vous pouvez utiliser cela et attraper une exception possible dans une fonction. Exemple:

une requête comme ci-dessus évite les exceptions possibles et est donc légèrement plus rapide.

to_regclass(rel_name) en Postgres 9.4 +

beaucoup plus simple maintenant:

SELECT to_regclass('schema_name.table_name');

même que la distribution, mais il retourne ...

... null plutôt que de lancer une erreur si le nom n'est pas trouvé

194
répondu Erwin Brandstetter 2017-05-23 11:54:41

peut-être utiliser information_schema :

SELECT EXISTS(
    SELECT * 
    FROM information_schema.tables 
    WHERE 
      table_schema = 'company3' AND 
      table_name = 'tableincompany3schema'
);
39
répondu kong 2013-12-14 13:45:23

pour PostgreSQL 9.3 ou moins...Ou qui aime tout normalisé au texte

trois saveurs de ma vieille bibliothèque SwissKnife: relname_exists(anyThing) , relname_normalized(anyThing) et relnamechecked_to_array(anyThing) . Tous les contrôles de pg_catalog.pg_class le tableau, et renvoie la norme universelle types de données ( boolean , texte ou texte []).

/**
 * 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(,'\.') t(x) -- not work with quoted names
     WHERE  CASE
              WHEN COALESCE(x[2],'')>'' THEN n.nspname = x[1]      AND c.relname = x[2]
              WHEN  IS NULL THEN           n.nspname = 'public'  AND c.relname = 
              ELSE                           n.nspname =         AND c.relname = 
            END
$f$ language SQL IMMUTABLE;

CREATE FUNCTION relname_exists(text,text default NULL) RETURNS boolean AS $wrap$
  SELECT EXISTS (SELECT relname_to_array(,))
$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(,), '.'), CASE WHEN  THEN '' ELSE NULL END)
$wrap$ language SQL IMMUTABLE;
0
répondu Peter Krauss 2017-11-18 01:39:07