sql - 如果类型转换失败则删除行

Question

好的，这是布局:
我有一堆 uuid 格式的 varchar 数据。我知道 uuid 是它自己的类型。这就是我得到数据的方式。因此，为了验证哪些是 uuid，我采用 varchar 类型的 uuid 并将其插入列为 的表中uuid。如果插入失败，则它不是 uuid 类型。我的基本问题是如果插入失败如何删除错误的 uuid。或者，如果在另一个表中插入失败，我如何从一个表中删除。
我的第一组数据:

drop table if exists temp1;
drop sequence if exists temp1_id_seq;
CREATE temp table temp1 (id serial, some_value varchar);
INSERT INTO temp1(some_value) 
SELECT split_part(name,':',2) FROM branding_resource WHERE name LIKE '%curric%';
create temp table temp2 (id serial, other_value uuid);

CREATE OR REPLACE function verify_uuid() returns varchar AS $$
DECLARE uu RECORD;
BEGIN
FOR uu IN  select * from temp1 
LOOP
    EXECUTE 'INSERT INTO temp2 values ('||uu.id||','''|| uu.some_value||''')';
END LOOP;
END;
$$
LANGUAGE 'plpgsql' ;
select verify_uuid();

当我运行它时，我得到了错误

ERROR: invalid input syntax for uuid:

这是我所期望的。我的数据集中有一些错误的 uuid。

我的研究让我找到了 Trapping Errors - Exceptions with UPDATE/INSERT在文档中。
缩小到重要部分:

BEGIN
FOR uu IN  select * from temp1 
LOOP
    begin
    EXECUTE 'INSERT INTO temp2 values ('||uu.id||','''|| uu.some_value||''')';
    return;
    exception when ??? then delete from temp1 where some_value = uu.some_value;
    end;
END LOOP;
END;

我不知道用什么代替???。我认为它与 ERROR: invalid input syntax for uuid: 有关，但我不确定。实际上，我什至不确定这是否是解决此问题的正确方法？

Answer 1

您可以使用 VERBOSE 模式从 psql 获取 SQLSTATE 代码，例如:

regress=> \set VERBOSITY verbose
regress=> SELECT 'fred'::uuid;
ERROR:  22P02: invalid input syntax for uuid: "fred"
LINE 1: SELECT 'fred'::uuid;
               ^
LOCATION:  string_to_uuid, uuid.c:129

在这里我们可以看到SQLSTATE是22P02。您可以直接在异常子句中使用它，但它通常对 look it up in the manual 更具可读性找到文本表示。在这里，我们看到 22P02 是 invalid_text_representation。

所以你可以写exception when invalid_text_representation then ...