在值子查询中指定列的数据类型 [英] Specifying data type of columns in a values subquery

问题描述

是否可以在值子查询中指定列的数据类型？像（values（...））这样的tmp_name（colname :: type）？

Is there a way to specify the data types of the columns in a values subquery? Something like (values (...)) as tmp_name (colname::type)?

说我有一个带有 uuid 列的表：

Say I have a table with a uuid column:

/* setup */
create table foo (id uuid);
insert into foo values 
    ('aaaabbbbccccddddeeeeffff00001111'),
    ('aaaabbbbccccddddeeeeffff00002222'),
    ('aaaabbbbccccddddeeeeffff00003333');

如果我尝试加入对其使用 values 子查询，postgres假定该列的类型为 text 并抛出一个类型错误（即使相同的字符串也可以直接插入 uuid 列中）：

If I try to join against it using a values subquery, postgres assumes the column is type text and throws a type error (even though the same strings worked for direct insertion into the uuid column) :

select foo.id from foo 
inner join (values 
    ('aaaabbbbccccddddeeeeffff00001111'),
    ('aaaabbbbccccddddeeeeffff00000000'))
as tmp (id) on foo.id = tmp.id;

ERROR:  operator does not exist: uuid = text
LINE 5:     as tmp (id) on foo.id = tmp.id;
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

显式强制转换每个值：

如果我将显式强制转换添加到值本身，则可以工作，但是不必强制转换每个单个值会很好值，然后整体指定列的数据类型：

It works if I add the explicit cast to the values themselves, but it would be nice not to have to cast every single value and just specify the datatype of the column as a whole:

select foo.id from foo 
inner join (values
    ('aaaabbbbccccddddeeeeffff00001111'::uuid),
    ('aaaabbbbccccddddeeeeffff00000000'::uuid))
as tmp (id) on foo.id = tmp.id;

推荐答案

您可以在连接谓词上强制转换一次：

You can cast once on the join predicate:

select foo.id from foo 
inner join (values 
    ('aaaabbbbccccddddeeeeffff00001111'),
    ('aaaabbbbccccddddeeeeffff00000000'))
as tmp (id) on foo.id = tmp.id::uuid;

这篇关于在值子查询中指定列的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！