PostgreSQL: How to check if a table exists in a given schema?



There are several ways to check the table exist in the particular schema, some solutions are described below.

First, I'll create an "Employee" table and give examples on it. Create the table using the following PostgreSQL statement.


CREATE TABLE employee
(
       id int generated always as identity,
       name text
);


Using information_schema check table existence in a schema


Syntax


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



schema_name, here you will put the schema name.

table_name, is you will put the table name.

For example, if I want to check the employee table exists or not in "public" schema, then execute the following query.


SELECT EXISTS (
   SELECT FROM information_schema.tables
   WHERE  table_schema = 'public'
   AND    table_name   = 'employee'
);


Result:

exists
-----
true

Now, I will check the table that does not exist in the "public" schema.


SELECT EXISTS (
   SELECT FROM information_schema.tables
   WHERE  table_schema = 'public'
   AND    table_name   = 'customer'
);


Result:

exists
-----
false

It means the customer table does not exist in the public schema.

Using system catalog check table existence in a schema



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


  
schema_name, here you will put the schema name.

table_name, is you will put the table name.

'r' means only tables (using pg_catalog you can check the particular object are exists or not like view, function, procedure, etc).


SELECT EXISTS (
   SELECT FROM pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = 'public'
   AND    c.relname = 'employee'
   AND    c.relkind = 'r'   
   );

  
exists
------
true

1 comment:

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...