Thread: Postgres - search for value throughout many tables?
Hi everyone,
I want to aks if anyone knows is there a way to search for specific "value" throughout list of tables OR all tables in databse?
Cheers,
czezz
On Wednesday, August 8, 2018, czezz <czezz@o2.pl> wrote:
Hi everyone,I want to aks if anyone knows is there a way to search for specific "value" throughout list of tables OR all tables in databse?
Can you pg_dump your database to plain text and search that? Nothing built in provides that ability though you possibly could work up something using dynamic sql.
David J.
If the num of tables is smallish you could run smth like select * from ( select table1::text as thecol from table1 UNION select table2::text FROM table2 UNION select table3::text FROM table3 UNION ..... ) as qry WHERE thecol ~* 'some pattern'; On 08/08/2018 17:09, czezz wrote: > Hi everyone, > I want to aks if anyone knows is there a way to search for specific "value" throughout list of tables OR all tables indatabse? > > Cheers, > czezz -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Hi, thanks that is actually a good hint :)
Dnia 8 sierpnia 2018 16:13 David G. Johnston <david.g.johnston@gmail.com> napisał(a):
On Wednesday, August 8, 2018, czezz <czezz@o2.pl> wrote:Hi everyone,I want to aks if anyone knows is there a way to search for specific "value" throughout list of tables OR all tables in databse?Can you pg_dump your database to plain text and search that? Nothing built in provides that ability though you possibly could work up something using dynamic sql.David J.
On Wed, 8 Aug 2018 at 10:14, David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Wednesday, August 8, 2018, czezz <czezz@o2.pl> wrote: >> >> Hi everyone, >> I want to aks if anyone knows is there a way to search for specific "value" throughout list of tables OR all tables indatabse? > > > Can you pg_dump your database to plain text and search that? Nothing built in provides that ability though you possiblycould work up something using dynamic sql. If there are some tables that are extraordinarily large that would not be good candidates, this could be excessively expensive. If you can identify a specific set of tables that are good candidates, then a faster option might involve: pg_dump --data-only --table=this_table --table=that_table --table=other_table databaseURI or, if there are only a few tables to omit... pg_dump --data-only --exclude-table=this_irrelevant_big_table --exclude-table=another_big_irrelevant_table databaseURI -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
On 08/08/2018 04:09 PM, czezz wrote: > Hi everyone, > I want to aks if anyone knows is there a way to search for specific > "value" throughout list of tables OR all tables in databse? > > Cheers, > czezz Hello, Maybe this article by Daniel could help you : https://blog-postgresql.verite.pro/2017/06/06/global-search.html Unfortunately it is not translated, you have to play with a translate tool. Regards,
Adrien NAYRAT wrote: > On 08/08/2018 04:09 PM, czezz wrote: > > Hi everyone, > > I want to aks if anyone knows is there a way to search for specific > > "value" throughout list of tables OR all tables in databse? > > > > Cheers, > > czezz > > Hello, > > Maybe this article by Daniel could help you : > https://blog-postgresql.verite.pro/2017/06/06/global-search.html > > Unfortunately it is not translated, you have to play with a translate tool. You may also get the code and README from here: https://github.com/dverite/postgresql-functions/tree/master/global_search Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite