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