Postgresql find value in all tables

PostgreSQL Query: Finding a Value in All Tables

In PostgreSQL, you can search for a specific value across all tables in a database using the following query:

    SELECT table_schema,
           table_name,
           column_name
    FROM information_schema.columns
    WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
          -- Exclude system catalog tables
      AND data_type IN ('character', 'character varying', 'text')
          -- Search only text-like column types
      AND table_name NOT LIKE 'pg_%'
          -- Exclude system-specific tables
      AND EXISTS (
            SELECT 1
            FROM <table_name>
            WHERE <column_name> ILIKE '%<search_value>%'
          );
  

Let’s break down the query:

• The query retrieves information from the information_schema.columns table which contains metadata about the columns of all tables in the database.
• table_schema NOT IN ('information_schema', 'pg_catalog') excludes system catalog tables from the search.
• data_type IN ('character', 'character varying', 'text') filters only text-like column types for the search. You can modify this condition based on your specific requirements.
• table_name NOT LIKE 'pg_%' excludes system-specific tables whose names start with ‘pg_’. You can adjust this condition to exclude other tables if needed.
• EXISTS (SELECT 1 FROM <table_name> WHERE <column_name> ILIKE '%<search_value>%') checks if the search value exists in any of the specified columns.

Replace the placeholders <table_name>, <column_name>, and <search_value> with your respective values.

Example:

Suppose we want to search for the value ‘apple’ in all tables and columns of a PostgreSQL database. The modified query would look like this:

    SELECT table_schema,
           table_name,
           column_name
    FROM information_schema.columns
    WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
          AND data_type IN ('character', 'character varying', 'text')
          AND table_name NOT LIKE 'pg_%'
          AND EXISTS (
            SELECT 1
            FROM <table_name>
            WHERE <column_name> ILIKE '%apple%'
          );
  

This query will return the schema, table, and column names where the value ‘apple’ is present.