问题

I'm providing the translated content you requested:

我正在尝试从多个表中以简洁的方式汇总数据。然而，我对Postgres或SQL并不很熟悉，所以我希望可以在这里得到一些帮助！

情况如下：我有包含文本的三列，来自两个不同的表。让我们称它们为表A和B。我想要在A.text1、A.text2和B.text3中搜索通配符'%postgres%'。我希望结果将所有包含该通配符的实例返回给我。

我想要的结果大致如下：

| 类型 | 文本 |
| --- | --- |
| text1 | ... postgres ... |
| text2 | ... postgres ... |
| text3 | ... postgres ... |

我的问题是：这是否可行，如果可行，您能指导我如何实现这样的结果吗？

我目前为此创建的解决方案是：

```sql
select a.text1, a.text2, b.text3
from a
inner join b
on a.id = b.aId
where a.text1 like '%postgres%'
or a.text2 like '%postgres%'
or b.text3 like '%postgres%'

这个方法有效，但结果有点丑陋。有很多单元格包含'[null]'，我不太喜欢这样。它看起来像这样：

更新：我已经解决了这个问题！我最终使用的SQL是：

select * from
(select 'text1' as type, text1 as text from a
union all
select 'text2' as type, text2 as text from a
union all
select 'text3' as type, text3 as text from b) as result
where result.text like '%postgres%'

Please note that I've provided the translated content as requested, without any additional information or answers to translation-related questions.

<details>
<summary>英文:</summary>

I&#39;m trying to aggregate data in a concise way from multiple tables. I&#39;m not very experienced with Postgres or SQL, however, so I was hoping I could get some help from here!

The situation is as follows: I have three columns containing text, from two different tables. Let&#39;s call them table A and B. I want to search A.text1, A.text2 and B.text3 for the wildcard &#39;%postgres%&#39;. I want the result to return all the instances containing that wildcard to me.

What I would like is a result that looks something like this:

| Type | Text |
| --- | --- |
| text1 | ... postgres ... |
| text2 | ... postgres ... |
| text3 | ... postgres ... |

My question is: is this feasible, and if it is feasible, can you point me in the right direction to achieve such a result?

The current solution I have created for this is:

```sql
select a.text1, a.text2, b.text3
from a
inner join b
on a.id = b.aId
where a.text1 like &#39;%postgres%&#39;
or a.text2 like &#39;%postgres%&#39;
or b.text3 like &#39;%postgres%&#39;

This works, but the result is kind of ugly. There are a lot of cells that contain '[null]' and I don't really like that. It looks like this:

Update: I have solved the problem! The sql I ended up with is:

select * from
(select 'text1' as type, text1 as text from a
union all
select 'text2' as type, text2 as text from a
union all
select 'text3' as type, text3 as text from b) as result
where result.text like '%postgres%'

答案1

得分: 1

以下是您要翻译的部分：

您的查询中的 UNION ALL 基本上回答了这个问题。我建议进行一些优化：

SELECT t.*
FROM   a
JOIN   LATERAL (
   VALUES
     ('text1', text1)
   , ('text2', text2)
   ) t (type, "text") ON "text" ~ 'postgres'

UNION ALL
SELECT 'text3', text3
FROM   b
WHERE  b.text3 ~ 'postgres';

通过这种方式，表 a 仅被扫描一次，而不是两次，成本大约减半。而且所有的行都会尽早过滤 - 这也更便宜。

关于 LATERAL ( ... VALUES ...)：

• https://stackoverflow.com/questions/47077068/postgres-convert-single-row-to-multiple-rows-unpivot/47077708#47077708

"text" ~ 'postgres' 等同于 "text" LIKE '%postgres%'。只有搜索字符串中的特殊字符可能会有所不同。请参见：

• https://stackoverflow.com/questions/5144036/escape-function-for-regular-expression-or-like-patterns/45741630#45741630

如果表很大，在文本列上使用三元组索引会有很大帮助。请参见：

• https://stackoverflow.com/questions/1566717/postgresql-like-query-performance-variations/13452528#13452528

我对标识符 "text" 进行了双引号引用，以避免与基本类型名称 text 的冲突。最好完全避免使用基本类型名称作为标识符。

SELECT t.*
FROM   a
JOIN   LATERAL (
   VALUES
     ('text1', text1)
   , ('text2', text2)
   ) t (type, "text") ON "text" ~ 'postgres'

UNION ALL
SELECT 'text3', text3
FROM   b
WHERE  b.text3 ~ 'postgres';