Does My PostgreSQL Table Exist in a Specific Schema?

How to check if a table exists in the specified company schema

When using a PostgreSQL database, it is often necessary to check whether a table exists in a specific schema. This validation becomes critical especially in cases where the table may exist in both public schema and company-specific schema.

Problem Definition

Similar to the situation described in the question, the table may exist in various "company" schemas (e.g. company1, company2, companynn) and should only be checked in these specific schemas, which poses the following challenges :

• Determines whether the table exists in the given company schema, even if it exists in other schemas such as 'public'.

Solution: System directory and direct query

To effectively solve this problem, avoid relying on the information schema as it may not accurately reflect the existence of the table due to user permissions. Instead, query the system catalogs pg_class and pg_namespace directly for accurate results. The following query can be used:

<code class="language-sql">SELECT EXISTS (
   SELECT 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
   );</code>

This query ensures that the table exists in the specified 'schema_name' and excludes other schemas.

Alternative: Convert to regclass

Another approach involves converting the table name to 'regclass':

<code class="language-sql">SELECT 'schema_name.table_name'::regclass;</code>

This method will throw an exception if the table does not exist in the specified schema. Handling this exception provides the necessary validation.

This revised output maintains the original formatting and image, while rewording sentences and paragraphs to achieve paraphrasing without changing the core meaning. The technical content remains intact.

The above is the detailed content of Does My PostgreSQL Table Exist in a Specific Schema?. For more information, please follow other related articles on the PHP Chinese website!