PostgreSQL Question posted in
The official documentation can be found here.

Search an entire database for a string in PostgreSQL

I am working in Postgresql and am trying to find what table a specific word is in. For example, I am trying to find out what table the word "banana" is in. The database is agriculture and schema is fruit. Is there a query I can run that would return what table banana is in?

Tags:

2

Answers


  1. 0

    Not efficient, but you can do it conveniently by converting the entire table to json and check that:

    SELECT 'mytable', json_agg(mytable)::text LIKE '%banana%' as contains_banana
FROM mytable

    You can check many tables at once:

    SELECT 'table1', json_agg(table1)::text LIKE '%banana%' as contains_banana FROM table1
UNION
SELECT 'table2', json_agg(table2)::text LIKE '%banana%' as contains_banana FROM table2
UNION
SELECT 'table3', json_agg(table3)::text LIKE '%banana%' as contains_banana FROM table3
    Login or Signup to reply.
  2. 0

    The following query will search for the value "banana" in all columns with a data type of either character varying or text in every table in the public schema:

    WITH
  search_tables AS (
    SELECT
      c.table_schema,
      c.table_name,
      STRING_AGG(FORMAT('s.%1$I::TEXT = p.search_word', c.column_name),
                 ' OR ' ORDER BY c.ordinal_position) AS search_criteria
    FROM
      information_schema.tables t
      JOIN information_schema.columns c
        ON c.table_schema = t.table_schema AND
           c.table_name = t.table_name
    WHERE
      t.table_type = 'BASE TABLE' AND
      t.table_schema = 'public' AND
      c.data_type IN ('character varying', 'text')
    GROUP BY
      c.table_schema, c.table_name
  )
SELECT
  st.table_schema, st.table_name
FROM
  search_tables st
  CROSS JOIN LATERAL XMLTABLE('/table/row'
                              PASSING QUERY_TO_XML(
                                        FORMAT(
                                          'SELECT true AS found_it
                                             FROM (VALUES (%1$L)) p(search_word)
                                             CROSS JOIN %2$I.%3$I s
                                             WHERE %4$s
                                             LIMIT 1',
                                          'banana',
                                          st.table_schema,
                                          st.table_name,
                                          st.search_criteria),
                                        FALSE,
                                        FALSE,
                                        '')
                              COLUMNS found_it BOOLEAN) x
  ORDER BY st.table_schema, st.table_name;

    Note that the search_criteria expression explicitly casts each searched column to type TEXT. This has been done so that the query can be easily modified to search non-text columns (e.g., JSON or XML). The cast has no adverse effects if the column is already character varying or text.

    The function, QUERY_TO_XML, enables executing dynamically generated queries without resorting to procedural code (e.g., PL/pgSQL). Because of XML’s low information density, QUERY_TO_XML is not practical for queries that return large data sets, but can be very useful for queries that return only a few rows.

    Changing = to ILIKE in the search_criteria expression and passing '%banana%' instead of 'banana' to FORMAT in the call to QUERY_TO_XML will perform a case-insensitive search for "banana" contained within a column.

    This query is likely to take a long time to execute if there are tables in the search space with a large number of rows. Modifying the criteria in the search_tables CTE to restrict the tables and columns included in the search space can help reduce the time to complete.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search