Postgres return boolean if exists

by

Query: Postgres – Return boolean if exists

Postgres supports multiple ways to check the existence of elements in a database. Here are some examples:

Check if a table exists:

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

The above query will return either true or false, indicating whether the table ‘my_table’ exists in the ‘public’ schema. If it exists, the result will be true; otherwise, it will be false.

Check if a column exists in a table:

    
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
        AND table_name = 'my_table'
        AND column_name = 'my_column'
      );

This query checks if a column named ‘my_column’ exists in the table ‘my_table’ within the ‘public’ schema.

Check if a schema exists:

    
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.schemata
        WHERE schema_name = 'my_schema'
      );

This query will determine if the schema ‘my_schema’ exists in the database.

Check if a function exists:

    
      SELECT EXISTS (
        SELECT 1
        FROM pg_proc
        WHERE proname = 'my_function'
      );

Here, the query checks whether the function named ‘my_function’ exists in the Postgres database.

Leave a comment