如何在PostgreSQL中查找与一对可为空的String列的组合相匹配的记录

bzzcjhmw 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(5)|浏览(98)

假设PostgreSQL表articles包含name和alt_name这两个可为空的String列。

*字符串name和alt_name的组合与同一表中相同类型的另一个组合匹配：

即[a.name, a.alt_name]等于[b.name, b.alt_name]或[b.alt_name, b.name]
其中name或alt_name可以是NULL或空String，并且在任何情况下NULL和空String都应被视为相同;
例如，当[a.name, a.alt_name] == ["abc", NULL]时，[b.name, b.alt_name] == ["", "abc"]的记录应该匹配，因为其中一个是"abc"，另一个是NULL或空字符串。

有什么简洁的查询可以实现这一点吗？
我想，如果有一种方法可以将两列连接起来，中间使用UTF-8 * 替换字符 *（U+FFFD），其中NULL被转换为空字符串，那么这个问题就可以解决了。比如，如果函数是magic_fn()，那么下面的代码就可以完成工作，前提是存在一个唯一的列id：

SELECT * FROM articles a INNER JOIN places b ON a.id <> b.id
  WHERE
        magic_fn(a.name, a.alt_name) =  magic_fn(b.name, b.alt_name)
     OR magic_fn(a.name, a.alt_name) =  magic_fn(b.alt_name, b.name);

-- [EDIT] corrected from the original post, which was simply wrong.

但是，concatnation is not a built-in function in PostgreSQL又不知道如何做到这一点。
[EDIT]正如@Serg和回答中所评论的，从版本9.1（CONCAT or ||）开始，PostgreSQL中现在提供了一个字符串连接函数;注意，它实际上接受非字符串输入，只要其中一个是Ver.15的字符串类型。
或者，也许根本就有更好的办法？

postgresql

来源：https://stackoverflow.com/questions/74403065/how-to-find-records-in-postgresql-matching-a-combination-of-a-pair-of-nullable-s

5条答案

按热度按时间

6jjcrrmo1#

您可以创建一个函数，该函数接受name和alt_name，然后返回一个聚合字符串，其中null转换为空字符串，并对结果进行排序：

create function magic_fn(a text, b text) returns text
  return (select json_agg(t.v) from (
    select t1.* from (
      select coalesce(a, '') v
      union all
      select coalesce(b, '') v) t1 
    order by t1.v) t);
create table articles (id int, name text, alt_name text);
insert into articles values (1, 'abc', null), (2, 'abc', ''), (3, null, 'abc'), (4, 'aaa', 'a'), (5, 'aaa', 'a'), (6, 'a', 'aaa')

用法：

select * from articles a join articles b 
on a.id <> b.id and magic_fn(a.name, a.alt_name) = magic_fn(b.name, b.alt_name)

See fiddle

赞(0）回复(0）举报 2022-11-23

rsl1atfo2#

试试这个

SELECT  *   FROM articles a
cross join articles b    
where  
(ARRAY[COALESCE(a.name,''),COALESCE(a.alt_name,'')] @>  ARRAY[COALESCE(b.name,''),COALESCE(b.alt_name,'')])  
and (ARRAY[COALESCE(a.name,''),COALESCE(a.alt_name,'')] <@  ARRAY[COALESCE(b.name,''),COALESCE(b.alt_name,'')]) 
and a.id<>b.id
and a.id<b.id  --optional (to avoid reverse matching)

db<>fiddle

赞(0）回复(0）举报 2022-11-23

vecaoik13#

回顾了几个答案（特别感谢@MitkoKeckaroski），我想出了这个简短的解决方案。COALESCE()没有必要！
条件是UTF取代字符（\U+FFFD）不应该出现在数据记录中，您可以根据Unicode规格放心地假设这一点。

SELECT * FROM articles a JOIN articles b 
ON a.id <> b.id AND
  ARRAY[CONCAT(a.name, U&'\FFFD', a.alt_name), 
        CONCAT(a.alt_name, U&'\FFFD', a.name)] @>
  ARRAY[CONCAT(b.name, U&'\FFFD', b.alt_name)];

请参见db<>fiddle（我在这里扩展了@Ajax1234准备的数据--谢谢！）

赞(0）回复(0）举报 2022-11-23

b4lqfgs44#

您可以尝试使用

将null转换为空合并
||用于连接字符串

然后比较类似sql：

(coalesce(a.name,'') || coalesce(a.altname,'')) =  (coalesce(b.name,'') || coalesce(b.altname,'')) 
 or 
 (coalesce(a.name,'') || coalesce(a.altname,'')) =  (coalesce(b.altname,'') || coalesce(b.name,''))

赞(0）回复(0）举报 2022-11-23

oxf4rvwz5#

您可以从这两个名称建立数组，移除null和空值，然后检查数组是否重叠（具有共同的元素）

select *
from articles
where array_remove(array[nullif(name,''), nullif(alt_name,'')], null) && array['abc']

通过创建一个生成这样的数组的函数，可以使这一点变得更容易：

create or replace function combine_names(p_names variadic text[]) 
  returns text[]
as
$$
  select array_agg(name)
  from unnest(p_names) as x(name)
  where nullif(trim(name),'') is not null;
$$ 
language sql
immutable
called on null input;

通过设置参数variadic，可以提供不同数量的参数（理论上甚至多于两个）

select *
from articles
where combine_names(name, alt_name) && combine_names('abc')

select *
from articles
where combine_names(name, alt_name) && combine_names('abc', null)

select *
from articles
where combine_names(name, alt_name) && combine_names('abc', 'def')

赞(0）回复(0）举报 2022-11-23

我来回答

如何在PostgreSQL中查找与一对可为空的String列的组合相匹配的记录

5条答案

相关问题

热门标签

最新问答