搜索整个postgres数据库？ [英] Search entire postgres database?

问题描述

我有一个庞大的数据库，我不介意离开搜索一段时间，但是由于各种原因，我无法转储整个数据库。我能写出的最简单的查询是什么，它将查询数据库中所有表的所有字段以查找特定的文本字符串？

I have a massive database that I don't mind leaving to search for a while, but I can't dump the whole database for various reasons. What is the easiest query I can write that will search all fields of all tables in a database for a specific string of text?

以下内容将不起作用，但应显示我希望看到的内容：

The following won't work but should demonstrate what I would like to see:

 SELECT * FROM * where * like '%mystring%'

有什么想法吗？

推荐答案

您可以利用以下事实：在PostgreSQL中，表名。*

You could take advantage of the fact that in PostgreSQL, tablename.* can be cast to text.

因此， SELECT t。* FROM表名t WHERE（t。*）:: text像'％somestring ％'将返回任何列包含 somestring 的行，无论该列的类型如何。

So, SELECT t.* FROM tablename t WHERE (t.*)::text LIKE '%somestring%' will return any row where any column contains somestring, no matter what the type of the column.

如果在 SELECT table_schema，table_name FROM information_schema 的循环中使用，它可与 grep 媲美在数据库转储中，除非您不需要转储。

If used in a loop on SELECT table_schema, table_name FROM information_schema, it's comparable to a grep within a database dump, except you don't need the dump.

这篇关于搜索整个postgres数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！