How to search a specific value in all tables PostgreSQL

Question

Is it possible to search every column of every table for a particular value in PostgreSQL   A similar question is available here for Oracle

User · Answer

-- Below function will list all the tables which contain a specific string in the database

 select TablesCount(‘StringToSearch’);

--Iterates through all the tables in the database

CREATE OR REPLACE FUNCTION **TablesCount**(_searchText TEXT)
RETURNS text AS 
$$ -- here start procedural part
   DECLARE _tname text;
   DECLARE cnt int;
   BEGIN
    FOR _tname IN SELECT table_name FROM information_schema.tables where table_schema='public' and table_type='BASE TABLE'  LOOP
         cnt= getMatchingCount(_tname,Columnames(_tname,_searchText));
                                RAISE NOTICE 'Count% ', CONCAT('  ',cnt,' Table name: ', _tname);
                END LOOP;
    RETURN _tname;
   END;
$$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification

-- Returns the count of tables for which the condition is met. -- For example, if the intended text exists in any of the fields of the table, -- then the count will be greater than 0. We can find the notifications -- in the Messages section of the result viewer in postgres database.

CREATE OR REPLACE FUNCTION **getMatchingCount**(_tname TEXT, _clause TEXT)
RETURNS int AS 
$$
Declare outpt text;
    BEGIN
    EXECUTE 'Select Count(*) from '||_tname||' where '|| _clause
       INTO outpt;
       RETURN outpt;
    END;
$$ LANGUAGE plpgsql;

--Get the fields of each table. Builds the where clause with all columns of a table.

CREATE OR REPLACE FUNCTION **Columnames**(_tname text,st text)
RETURNS text AS 
$$ -- here start procedural part
DECLARE
                _name text;
                _helper text;
   BEGIN
                FOR _name IN SELECT column_name FROM information_schema.Columns WHERE table_name =_tname LOOP
                                _name=CONCAT('CAST(',_name,' as VarChar)',' like ','''%',st,'%''', ' OR ');
                                _helper= CONCAT(_helper,_name,' ');
                END LOOP;
                RETURN CONCAT(_helper, ' 1=2');

   END;
$$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification

User · Answer

Without storing a new procedure you can use a code block and execute to obtain a table of occurences  You can filter results by schema  table or column name   DO    DECLARE   value int    0    sql text     The constructed select statement     rec1 record    rec2 record  BEGIN   DROP TABLE IF EXISTS  x    CREATE TEMPORARY TABLE  x       schema name text       table name text       column name text      found text        FOR rec1 IN          SELECT table schema  table name  column name         FROM information schema columns          WHERE table name  lt  gt    x                  AND UPPER column name  LIKE UPPER                                         AND table schema  lt  gt   pg catalog                  AND table schema  lt  gt   information schema                  AND data type IN   character varying    text    character    char    varchar           LOOP     sql    concat  SELECT    rec1  column name     AS  found  FROM   rec1  table schema        rec1  table name      WHERE UPPER   rec1  column name       LIKE UPPER       my substring to find goes here                 RAISE NOTICE      sql      BEGIN         FOR rec2 IN EXECUTE sql LOOP             RAISE NOTICE      sql              INSERT INTO  x VALUES  rec1  table schema   rec1  table name   rec1  column name   rec2  found            END LOOP      EXCEPTION WHEN OTHERS THEN     END    END LOOP    END       SELECT   FROM  x

User · Answer

to search every column of every table for a particular value   This does not define how to match exactly  Nor does it define what to return exactly   Assuming    Find any row with any column containing the given value in its text representation - as opposed to equaling the given value  Return the table name  regclass  and the tuple ID  ctid   because that s simplest    Here is a dead simple  fast and slightly dirty way   CREATE OR REPLACE FUNCTION search whole db  like pattern text    RETURNS TABLE  tbl regclass   ctid tid  AS  func  BEGIN    FOR  tbl IN       SELECT c oid  regclass       FROM   pg class c       JOIN   pg namespace n ON n oid   relnamespace       WHERE  c relkind    r                            -- only tables       AND    n nspname       pg  information schema    -- exclude system schemas       ORDER BY n nspname  c relname    LOOP       RETURN QUERY EXECUTE format            SELECT  1  ctid FROM  s t WHERE t  text     L            tbl          like pattern               USING  tbl     END LOOP  END  func   LANGUAGE plpgsql    Call   SELECT   FROM search whole db  mypattern      Provide the search pattern without enclosing     Why slightly dirty   If separators and decorators for the row in text representation can be part of the search pattern  there can be false positives    column separator    by default whole row is enclosed in parentheses    some values are enclosed in double quotes     may be added as escape char   And the text representation of some columns may depend on local settings - but that ambiguity is inherent to the question  not to my solution   Each qualifying row is returned once only  even when it matches multiple times  as opposed to other answers here    This searches the whole DB except for system catalogs  Will typically take a long time to finish  You might want to restrict to certain schemas   tables  or even columns  like demonstrated in other answers  Or add notices and a progress indicator  also demonstrated in another answer   The regclass object identifier type is represented as table name  schema-qualified where necessary to disambiguate according to the current search path    Find the referenced table name using table  field and schema name   What is the ctid    How do I decompose ctid into page and row numbers    You might want to escape characters with special meaning in the search pattern  See    Escape function for regular expression or LIKE patterns

User · Answer

If you re using IntelliJ add your DB to Database view then right click on databases and select full text search  it will list all tables and all fields for your specific text

User · Answer

There is a way to achieve this without creating a function or using an external tool  By using Postgres  query to xml   function that can dynamically run a query inside another query  it s possible to search a text across many tables  This is based on my answer to retrieve the rowcount for all tables   To search for the string foo across all tables in a schema  the following can be used   with found rows as     select format   I  I   table schema  table name  as table name           query to xml format  select to jsonb t  as table row                                from  I  I as t                                where t  text like     foo        table schema  table name                          true  false      as table rows   from information schema tables    where table schema    public    select table name  x table row from found rows f   left join xmltable    table row                        passing table rows                        columns                          table row text path  table row   as x on true   Note that the use of xmltable requires Postgres 10 or newer  For older Postgres version  this can be also done using xpath     with found rows as     select format   I  I   table schema  table name  as table name           query to xml format  select to jsonb t  as table row                                from  I  I as t                                where t  text like     foo        table schema  table name                          true  false      as table rows   from information schema tables    where table schema    public    select table name  x table row from found rows f    cross join unnest xpath   table row table row text     table rows   as r data    The common table expression  WITH      is only used for convenience  It loops through all tables in the public schema  For each table the following query is run through the query to xml   function   select to jsonb t  from some table t where t  text like   foo      The where clause is used to make sure the expensive generation of XML content is only done for rows that contain the search string  This might return something like this    lt table xmlns xsi  http   www w3 org 2001 XMLSchema-instance  gt   lt row gt     lt table row gt   id   42   some column    foobar   lt  table row gt   lt  row gt   lt  table gt    The conversion of the complete row to jsonb is done  so that in the result one could see which value belongs to which column    The above might return something like this   table name       table row ------------- ---------------------------------------- public foo        id   1   some column    foobar   public bar        id   42   another column    barfoo     Online example for Postgres 10   Online example for older Postgres versions

User · Answer

Here s a pl pgsql function that locates records where any column contains a specific value  It takes as arguments the value to search in text format  an array of table names to search into  defaults to all tables  and an array of schema names  defaults all schema names    It returns a table structure with schema  name of table  name of column and pseudo-column ctid  non-durable physical location of the row in the table  see System Columns   CREATE OR REPLACE FUNCTION search columns      needle text      haystack tables name   default           haystack schema name   default        RETURNS table schemaname text  tablename text  columnname text  rowctid text  AS    begin   FOR schemaname tablename columnname IN       SELECT c table schema c table name c column name       FROM information schema columns c         JOIN information schema tables t ON            t table name c table name AND t table schema c table schema          JOIN information schema table privileges p ON            t table name p table name AND t table schema p table schema               AND p privilege type  SELECT           JOIN information schema schemata s ON            s schema name t table schema        WHERE  c table name ANY haystack tables  OR haystack tables               AND  c table schema ANY haystack schema  OR haystack schema               AND t table type  BASE TABLE    LOOP     FOR rowctid IN       EXECUTE format  SELECT ctid FROM  I  I WHERE cast  I as text   L          schemaname         tablename         columnname         needle             LOOP       -- uncomment next line to get some progress report       -- RAISE NOTICE  hit in       schemaname  tablename        RETURN NEXT      END LOOP   END LOOP  END     language plpgsql    See also the version on github based on the same principle but adding some speed and reporting improvements   Examples of use in a test database    Search in all tables within public schema     select   from search columns  foobar     schemaname   tablename   columnname   rowctid  ------------ ----------- ------------ ---------  public       s3          usename       0 11   public       s2          relname       7 29   public       w           body          0 2   3 rows     Search in a specific table      select   from search columns  foobar    w      schemaname   tablename   columnname   rowctid  ------------ ----------- ------------ ---------  public       w           body          0 2   1 row     Search in a subset of tables obtained from a select     select   from search columns  foobar   array select table name  name from information schema tables where table name like  s     array  public      schemaname   tablename   columnname   rowctid  ------------ ----------- ------------ ---------  public       s2          relname       7 29   public       s3          usename       0 11   2 rows     Get a result row with the corresponding base table and and ctid     select   from public w where ctid   0 2     title    body            tsv          ------- -------- ---------------------  toto    foobar    foobar  2  toto  1   Variants   To test against a regular expression instead of strict equality  like grep  this part of the query   SELECT ctid FROM  I  I WHERE cast  I as text   L  may be changed to   SELECT ctid FROM  I  I WHERE cast  I as text     L For case insensitive comparisons  you could write   SELECT ctid FROM  I  I WHERE lower cast  I as text     lower  L

User · Answer

Here s  Daniel V  rit   s function with progress reporting functionality  It reports progress in three ways    by RAISE NOTICE  by decreasing value of supplied  progress seq  sequence from  total number of colums to search in  down to 0  by writing the progress along with found tables into text file  located in c  windows temp  progress seq  txt       CREATE OR REPLACE FUNCTION search columns      needle text      haystack tables name   default           haystack schema name   default   public        progress seq text default NULL   RETURNS table schemaname text  tablename text  columnname text  rowctid text  AS    DECLARE currenttable text  columnscount integer  foundintables text    foundincolumns text    begin currenttable     columnscount    SELECT count 1        FROM information schema columns c       JOIN information schema tables t ON          t table name c table name AND t table schema c table schema        WHERE  c table name ANY haystack tables  OR haystack tables               AND c table schema ANY haystack schema          AND t table type  BASE TABLE    integer  PERFORM setval progress seq  regclass  columnscount      FOR schemaname tablename columnname IN       SELECT c table schema c table name c column name       FROM information schema columns c       JOIN information schema tables t ON          t table name c table name AND t table schema c table schema        WHERE  c table name ANY haystack tables  OR haystack tables               AND c table schema ANY haystack schema          AND t table type  BASE TABLE    LOOP     EXECUTE format  SELECT ctid FROM  I  I WHERE cast  I as text   L          schemaname         tablename         columnname         needle       INTO rowctid      IF rowctid is not null THEN       RETURN NEXT        foundintables   foundintables    tablename        foundincolumns   foundincolumns    columnname        RAISE NOTICE  FOUND               schemaname tablename columnname  rowctid      END IF           IF  progress seq IS NOT NULL  THEN          PERFORM nextval progress seq  regclass       END IF      IF currenttable lt  gt tablename  THEN       currenttable tablename       IF  progress seq IS NOT NULL  THEN          RAISE NOTICE  Columns left to look in     looking in table      currval progress seq  regclass   tablename          EXECUTE  COPY  SELECT unnest string to array   Current table  column      columnscount-currval progress seq  regclass       of      columnscount             tablename      n nFound in tables columns  n     COALESCE           SELECT string agg c1           c2    n   FROM  SELECT unnest foundintables  AS c1 unnest foundincolumns  AS c2  AS t1                          n      TO   c  WINDOWS temp      progress seq      txt         END IF      END IF   END LOOP  END     language plpgsql

User · Answer

How about dumping the contents of the database  then using grep     pg dump --data-only --inserts -U postgres your-db-name  gt  a tmp   grep United a tmp INSERT INTO countries VALUES   US    United States    INSERT INTO countries VALUES   GB    United Kingdom      The same utility  pg dump  can include column names in the output  Just change --inserts to --column-inserts  That way you can search for specific column names  too  But if I were looking for column names  I d probably dump the schema instead of the data     pg dump --data-only --column-inserts -U postgres your-db-name  gt  a tmp   grep country code a tmp INSERT INTO countries  iso country code  iso country name  VALUES   US    United  States    INSERT INTO countries  iso country code  iso country name  VALUES   GB    United Kingdom

User · Answer

And if someone think it could help  Here is  Daniel V  rit   s function  with another param that accept names of columns that can be used in search  This way it decrease the time of processing  At least in my test it reduced a lot   CREATE OR REPLACE FUNCTION search columns      needle text      haystack columns name   default           haystack tables name   default           haystack schema name   default   public     RETURNS table schemaname text  tablename text  columnname text  rowctid text  AS    begin   FOR schemaname tablename columnname IN       SELECT c table schema c table name c column name       FROM information schema columns c       JOIN information schema tables t ON          t table name c table name AND t table schema c table schema        WHERE  c table name ANY haystack tables  OR haystack tables               AND c table schema ANY haystack schema          AND  c column name ANY haystack columns  OR haystack columns               AND t table type  BASE TABLE    LOOP     EXECUTE format  SELECT ctid FROM  I  I WHERE cast  I as text   L          schemaname         tablename         columnname         needle       INTO rowctid      IF rowctid is not null THEN       RETURN NEXT      END IF   END LOOP  END     language plpgsql    Bellow is an example of usage of the search function created above   SELECT   FROM search columns  86192700        array SELECT DISTINCT a column name  name FROM information schema columns AS a             INNER JOIN information schema tables as b ON  b table catalog   a table catalog AND b table schema   a table schema AND b table name   a table name          WHERE              a column name iLIKE   cep                AND b table type    BASE TABLE              AND b table schema    public               array SELECT b table name  name FROM information schema columns AS a             INNER JOIN information schema tables as b ON  b table catalog   a table catalog AND b table schema   a table schema AND b table name   a table name          WHERE              a column name iLIKE   cep                AND b table type    BASE TABLE              AND b table schema    public

[postgresql] How to search a specific value in all tables (PostgreSQL)?

Examples related to postgresql

Examples related to grep

Examples related to string-matching