Postgres - search for value throughout many tables?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres - search for value throughout many tables?

czezz
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
Reply | Threaded
Open this post in threaded view
|

Re: Postgres - search for value throughout many tables?

David G Johnston
On Wednesday, August 8, 2018, czezz <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Postgres - search for value throughout many tables?

Achilleas Mantzios
In reply to this post by czezz
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 in databse?
>
> Cheers,
> czezz


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt


Reply | Threaded
Open this post in threaded view
|

Re: Postgres - search for value throughout many tables?

Christopher Browne-3
In reply to this post by David G Johnston
On Wed, 8 Aug 2018 at 10:14, David G. Johnston
<[hidden email]> wrote:
>
> On Wednesday, August 8, 2018, czezz <[hidden email]> 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.

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?"

Reply | Threaded
Open this post in threaded view
|

Re: Postgres - search for value throughout many tables?

Adrien Nayrat-2
In reply to this post by czezz
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,

Reply | Threaded
Open this post in threaded view
|

Re: Postgres - search for value throughout many tables?

Daniel Verite
        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

Previous Thread Next Thread