如何选择带有左外部联接的最小U​​UID? [英] How to select minimum UUID with left outer join?

问题描述

我正在尝试从表中选择一行，

I'm trying to select a row from a table which:

1. 具有最小UUID
2. 未在另一个表中引用

但是当我尝试强制执行第一个约束时遇到了问题.

But I'm having problems when I try to enforce the first constraint.

这一切都按预期工作在整数上: 首先，创建如下所示的表:

Here's everything working as expected on integers: First, create tables that look like this:

t1
+----+---------+
| id | content |
+----+---------+
|  1 | a       |
|  2 | b       |
|  3 | c       |
+----+---------+

和

t2
+----+---------+
| id | t1_id   |
+----+---------+
|  1 | 1       |
+----+---------+

postgres=# create table t1(id int, content varchar(10), primary key (id));
CREATE TABLE
postgres=# create table t2(id int, t1_id int, foreign key (t1_id) references t1(id));
CREATE TABLE
postgres=# insert into t1 values (1, 'a');
INSERT 0 1
postgres=# insert into t1 values (2, 'b');
INSERT 0 1
postgres=# insert into t1 values (3, 'c');
INSERT 0 1
postgres=# insert into t2 values (1, 1);
INSERT 0 1

现在，我想选择t1中具有最低id的行，该行在t2中不会显示为外键.我想在t1中选择具有id = 2的行，它按预期方式工作:

Now, I want to select the row in t1 with the lowest id which doesn't appear as a foreign key in t2. I want to select the row in t1 which has id = 2 and it works as expected:

postgres=# select min(t1.id) from t1 left outer join t2 on t1.id = t2.t1_id where t2.id is null;
 min
-----
   2
(1 row)

但是，当我尝试使用UUID进行相同操作时，最终查询根本无法返回任何内容.请注意，我已经使用了这篇文章中的答案来定义查找最小UUID的方法:

However, when I try the same with UUIDs, the final query fails to return anything at all. Note, I've used the answer from this post to define a way to find minimum UUIDs:

CREATE OR REPLACE FUNCTION min(uuid, uuid)
RETURNS uuid AS $$
BEGIN
    IF $2 IS NULL OR $1 > $2 THEN
        RETURN $2;
    END IF;

    RETURN $1;
END;
$$ LANGUAGE plpgsql;


create aggregate min(uuid) (
  sfunc = min,
  stype = uuid,
  combinefunc = min,
  parallel = safe,
  sortop = operator (<)
);

现在，与以前一样构建表，使用 gen_random_uuid 自动生成UUID:

Now, build the tables just the same as before, use gen_random_uuid to autogenerate UUIDs:

postgres=# drop table t2;
postgres=# drop table t1;
postgres=# create table t1(id uuid default gen_random_uuid(), content varchar(10), primary key (id));
postgres=# create table t2(id int, t1_id uuid, foreign key (t1_id) references t1(id));
postgres=# insert into t1(content) ('a');
postgres=# insert into t1(content) values ('a');
postgres=# insert into t1(content) values ('b');
postgres=# insert into t1(content) values ('c');

我们已经成功地在t1中输入了三个条目.在t2中添加一个条目:

We've successfully made three entries in t1. Add an entry to t2:

postgres=# select * from t1;
                  id                  | content
--------------------------------------+---------
 b6148ae3-db56-4a4a-8d46-d5b4f04277ac | a
 03abd324-8626-4fb1-9cb0-593373abf9ca | b
 9f12b297-3f60-48a7-8282-e27c3aff1152 | c
(3 rows)


postgres=# insert into t2 values(1, '9f12b297-3f60-48a7-8282-e27c3aff1152');

尝试从t1中选择具有最小ID且未出现在t2中的行，请注意，这会失败.

Try to select the row from t1 with a minimum ID that doesn't appear in t2, note that this fails.

postgres=# select min(t1.id) from t1 left outer join t2 on t1.id = t2.t1_id where t2.id is null;
 min
-----

(1 row)

在这里，我们表明我们可以选择t1中的两个未引用条目，并且可以独立选择一个最小的UUID:

Here we show that we can select the two unreferenced entries in t1 and we can select a minimum UUID independently:

postgres=# select t1.id from t1 left outer join t2 on t1.id = t2.t1_id where t2.id is null;
                  id
--------------------------------------
 03abd324-8626-4fb1-9cb0-593373abf9ca
 b6148ae3-db56-4a4a-8d46-d5b4f04277ac
(2 rows)

postgres=# select min(id) from t1;
                 min
--------------------------------------
 03abd324-8626-4fb1-9cb0-593373abf9ca
(1 row)

因此，当我尝试选择最小的UUID并尝试执行左外部联接时，会发生一些有趣的事情.

So, something funny goes on when I try to select a minimum UUID while also trying to perform the left outer join.

使用not exists时存在相同的问题:

the same problem exists when using not exists:

postgres=# select min(id) from t1 where not exists (select t1_id from t2 where t2.t1_id = t1.id);
 min
-----

(1 row)

，但是使用not in时不会出现此问题:

but the problem doesn't appear when using not in:

postgres=# select min(id) from t1 where id not in (select t1_id from t2);
                 min
--------------------------------------
 03abd324-8626-4fb1-9cb0-593373abf9ca
(1 row)

推荐答案

找到了一个解决方案，从

Found a solution, turns out the function comparing UUIDs from this post isn't correct. Here's the function I wrote, which is probably less performant, which passes all the cases it failed at before:

    CREATE FUNCTION min_uuid(uuid, uuid)
    RETURNS uuid AS $$
    BEGIN
        -- if they're both null, return null
        IF $2 IS NULL AND $1 IS NULL THEN
            RETURN NULL ;
        END IF;

        -- if just 1 is null, return the other
        IF $2 IS NULL THEN
            RETURN $1;
        END IF ;
        IF $1 IS NULL THEN
            RETURN $2;
          END IF;

        -- neither are null, return the smaller one
        IF $1 > $2 THEN
            RETURN $2;
        END IF;

        RETURN $1;
    END;
    $$ LANGUAGE plpgsql;


    create aggregate min(uuid) (
      sfunc = min_uuid,
      stype = uuid,
      combinefunc = min_uuid,
      parallel = safe,
      sortop = operator (<)
    );

这篇关于如何选择带有左外部联接的最小U​​UID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！