This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the different options to check weather your table exists in your database or not.
For checking the existence of table is a ubiquitous requirement for PostgreSQL Database Developer.
PostgreSQL 9.4 introduced to_regclass to check object presence very efficiently.
You can check other below options as well.
Use to_regclass (From PostgreSQL 9.4): This is the fastest option.
|
1 |
SELECT to_regclass('schema_name.table_name'); |
Use information_schema.tables:
|
1 2 3 4 5 6 7 |
SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name = 'table_name' ); |
Use catalogs tables:
|
1 2 3 4 5 6 7 8 9 |
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 ); |
Use pg_tables catalog:
|
1 2 3 4 5 6 7 |
SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = 'schema_name' AND tablename = 'table_name' ); |
