Specifying data type of columns in a values subque

2020-04-11 18:21发布

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

Contrived Example

Say I have a table with a uuid column:

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


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.


Explicitly casting each value:

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;

1条回答
我只想做你的唯一
2楼-- · 2020-04-11 18:44

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;
查看更多
登录 后发表回答