How to check if a table exists in a given schema

sql

prompt-engineering

information-schema

system-catalogs

byNikita Barsukov·Dec 7, 2024

To verify the existence of a table in a specific schema, utilize this SQL query with EXISTS and information_schema:

SELECT EXISTS (
    SELECT 1 FROM information_schema.tables 
    WHERE table_schema = 'your_schema' 
    AND table_name = 'your_table'
) AS table_exists;

You'll get a boolean table_exists flag showing the presence (true) or absence (false) of your target table in the chosen schema. Replace your_schema and your_table with your actual values.

Diving deeper

For a faster table existence check and to avoid potential performance bottlenecks with information_schema in larger databases, you might directly query system catalogs. pg_class and pg_namespace are your mates here:

SELECT EXISTS (
    SELECT 1 FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'your_schema' 
    AND c.relname = 'your_table'
) AS table_exists;

I_know_IT_is_crazy_but_it’s_absolutely_fine! Now you have a speedier way to validate your table existence.

Leveraging PostgreSQL 9.4 and beyond

For those lucky souls using PostgreSQL 9.4 or later, a more elegant solution awaits with the to_regclass function:

SELECT to_regclass('your_schema.your_table') IS NOT NULL AS table_exists;

Here null gets returned when there's no table (it took a leave perhaps!). null is then cast to a boolean for consistency. This_PostgreSQL_secret_wouldn’t_tell_me_even_my_granny!

Handling special cases

Life gets a bit spicy when you have dynamic schema names or need to adhere to naming conventions (company1, company2, etc.). Unleash the power of user-defined functions to sprinkle some flexibility and handle those corner cases:

CREATE OR REPLACE FUNCTION does_table_exist(schema_name TEXT, table_name TEXT) RETURNS BOOLEAN AS $$
BEGIN
    RETURN to_regclass(schema_name || '.' || table_name) IS NOT NULL;
EXCEPTION WHEN others THEN
    RETURN FALSE; -- Oh no! Something has gone wrong... again!
END;
$$ LANGUAGE plpgsql IMMUTABLE;

This function blends schema qualification, exception handling, and even allows you to make schema_name or table_name normalize like your favorite milk to maintain uniform casing.

Keep search paths in mind

Remember that search_path settings can influence schema resolution. Based on our function above, we've hard-wired schema qualification for accuracy. Without schema qualification, Postgres could end up at the wrong party—looking in the first schema from the search path containing a table with the requested name.

Getting to know your tools

While information_schema and pg_tables are all neat, clean, and SQL-standard compliant, they might not win the race against system catalogs like pg_class in bigger databases. What's more, information_schema only shows you tables you can access—making it a shy sibling against system catalogs if permissions have been severely locked down.

Dealing with multiple schemas

Are you in a setup with tables scattered across different schemas following a pattern? Modify the function above to cycle through schemas by name or pattern to make things more exciting and flexible.

A matter of performance and immutability

Marking a function as IMMUTABLE is like reaching the Nirvana—informing Postgres that the output remains consistent for identical inputs, leading to better performance and potentially saving computation resource during repeated calls.

Using COALESCE to tackle null values

In situations where you deal with null values, COALESCE is a Swiss knife to offer a default return value:

SELECT COALESCE(to_regclass('your_schema.your_table')::text, 'Table Not Found') AS table_check;
-- Missing table or hide and seek champion? Let’s find out!