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
set search_path='Schema_Name';
ReplyDelete/dt table_name