回复： BUG #18173: ERROR: could not identify a comparison function for type unknown

Thank you very much for your reply.

when remove the parentheses, it is OK.
because plan is diffrent:

postgres=# explain select distinct id,name,'D3Q84xpymM',123,'123' from test_v;
                            QUERY PLAN                            
------------------------------------------------------------------
 HashAggregate  (cost=28.75..30.75 rows=200 width=105)
   Group Key: id, name
   ->  Seq Scan on test_v  (cost=0.00..22.50 rows=1250 width=105)
(3 rows)

postgres=# explain select distinct (id,name,'D3Q84xpymM',123,'123') from test_v;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Unique  (cost=86.80..93.05 rows=200 width=32)
   ->  Sort  (cost=86.80..89.92 rows=1250 width=32)
         Sort Key: (ROW(id, name, 'D3Q84xpymM', 123, '123'))
         ->  Seq Scan on test_v  (cost=0.00..22.50 rows=1250 width=32)
(4 rows)

Sometimes users just want to use parentheses, because  they were used on Oracle like this  before.

Thanks!

------------------ 原始邮件 ------------------
发件人: "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at&gt;;
发送时间:&nbsp;2023年10月31日(星期二) 晚上7:10
收件人:&nbsp;"下雨天"<409800246(at)qq(dot)com&gt;;"pgsql-bugs"<pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;;

主题:&nbsp;Re: BUG #18173: ERROR: could not identify a comparison function for type unknown

On Mon, 2023-10-30 at 13:24 +0000, PG Bug reporting form wrote:
&gt; PostgreSQL version: 16.0
&gt;
&gt; postgres=# CREATE TABLE test_v(id int,name varchar(30));
&gt; CREATE TABLE
&gt; postgres=# insert into test_v values(9,'abc'),(9,'def'),(9,'gh'),
&gt; (9,'gh');
&gt; INSERT 0 4
&gt; postgres=# explain (costs off) select distinct
&gt; (id,name,'D3Q84xpymM',123,'123') from test_v;
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&gt; -------------------------------------------------------------
&gt;&nbsp; Unique
&gt;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Sort
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: (ROW(id, name, 'D3Q84xpymM', 123, '123'))
&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on test_v
&gt; (4 rows)
&gt;
&gt; postgres=# select distinct (id,name,'D3Q84xpymM',123,'123') from test_v;
&gt; ERROR:&nbsp; could not identify a comparison function for type unknown

This is not a bug.&nbsp; You probably want to remove the parentheses.

Yours,
Laurenz Albe