Posted By: Anonymous
Postgres 8.4 and greater databases contain common tables in
public schema and company specific tables in
company schema names always start with
'company' and end with the company number.
So there may be schemas like:
public company1 company2 company3 ... companynn
An application always works with a single company.
search_path is specified accordingly in odbc or npgsql connection string, like:
How would you check if a given table exists in a specified
In any case, the function should check only
companyn schema passed, not other schemas.
If a given table exists in both
public and the passed schema, the function should return
It should work for Postgres 8.4 or later.
It depends on what you want to test exactly.
To find “whether the table exists” (no matter who’s asking), querying the information schema (
information_schema.tables) is incorrect, strictly speaking, because (per documentation):
Only those tables and views are shown that the current user has access
to (by way of being the owner or having some privilege).
The query provided by @kong can return
FALSE, but the table can still exist. It answers the question:
How to check whether a table (or view) exists, and the current user has access to it?
SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = 'schema_name' AND table_name = 'table_name' );
The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (
information_schema.tables is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs – which actually carry all information.
Your question was:
How to check whether a table exists?
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' -- only tables );
Use the system catalogs
pg_namespace directly, which is also considerably faster. However, per documentation on
pg_classcatalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
pg_index), sequences, views, materialized views, composite
types, and TOAST tables;
For this particular question you can also use the system view
pg_tables. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):
SELECT EXISTS ( SELECT FROM pg_tables WHERE schemaname = 'schema_name' AND tablename = 'table_name' );
Identifiers have to be unique among all objects mentioned above. If you want to ask:
How to check whether a name for a table or similar object in a given schema is taken?
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' );
Alternative: cast to
This raises an exception if the (optionally schema-qualified) table (or other object occupying that name) does not exist.
If you do not schema-qualify the table name, a cast to
regclass defaults to the
search_path and returns the OID for the first table found – or an exception if the table is in none of the listed schemas. Note that the system schemas
pg_temp (the schema for temporary objects of the current session) are automatically part of the
You can use that and catch a possible exception in a function. Example:
A query like above avoids possible exceptions and is therefore slightly faster.
to_regclass(rel_name) in Postgres 9.4+
Much simpler now:
Same as the cast, but it returns …
… null rather than throwing an error if the name is not found