How to check table exists or not in PostgreSQL database

We can check whether table exists or not using information_schema.tables,pg_catalog.pg_class, pg_tables, using meta command or to_regclass.

In this tutorials, we are going to check table named ‘accounts’ exists or not in ‘bank’ database.

1) Using information_schema.tables.

Syntax:

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

Example: If output of following query is t, which means table exists otherwise ‘f’ means table doesnt exists.

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

Output:

exists -------- f (1 row)

2) Using catalogs tables.

Syntax:

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' -- r = tables );

Example:If output of following query is t, which means table exists otherwise ‘f’ means table doesnt exists.

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

Output:

exists -------- t (1 row)

3) Using pg_tables catalog:

Syntax:

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

Example: If output of following query is t, which means table exists otherwise ‘f’ means table doesnt exists.

SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'accounts' );

Output:

exists -------- t (1 row)

4) Using to_regclass:

Syntax:

SELECT to_regclass('schema.table_name');

Example: If output of following query is tablename, which means table exists otherwise(no output) means table doesnt exists.

SELECT to_regclass('public.accounts');

Output:

to_regclass ------------- accounts (1 row)

5) Using meta-command :

Syntax:

\dt+ table_name;

Example: If table exists, it provide us the table information otherwise it will through error like “No matching relations found.”

\dt+ accounts

Output:

Schema | Name | Type | Owner | Size | Description --------+----------+-------+----------+------------+------------- public | accounts | table | postgres | 8192 bytes | (1 row)

Related Posts

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: