Server does not recognize a custom type literal-CodePudding

I have created a custom type in postgres:

CREATE TYPE semanticversion AS (
    major integer, 
    minor integer, 
    patch integer, 
    prerelease text, 
    prerelease_number integer);

And populated some data (select * from TABLE order by id):

 id |   version   
---- -------------
  1 | (1,0,0,,)
  2 | (2,0,0,,)
  3 | (1,2,3,,)
  4 | (1,2,3,b,1)
  5 | (1,2,3,d,1)
  6 | (1,2,3,h,1)

When I order by this field, it works as expected (select * from TABLE order by version):

 id |   version   
---- -------------
  1 | (1,0,0,,)
  4 | (1,2,3,b,1)
  5 | (1,2,3,d,1)
  6 | (1,2,3,h,1)
  3 | (1,2,3,,)
  2 | (2,0,0,,)

But when I try a cutoff (select * from TABLE where version < (1,2,3,'h',1)), it throws an error:

ERROR:  cannot compare dissimilar column types text and unknown at record column 4

I find this confusing, since it is clearly able to compare these values, otherwise it would not be able to successfully order them.

CodePudding user response：

The type conversion mechanism would need two passes to resolve this case implicitly. Use one of explicit casts:

where version < (1,2,3,'h'::text,1)
-- or
where version < (1,2,3,'h',1)::semanticversion
-- or
where version < '(1,2,3,h,1)'::semanticversion

CodePudding user response：

Not sure what that h in the WHERE clause is supposed to reference, but apart from that it all seems to work for me.

richard=> SELECT * FROM t ORDER BY v;
      v      
-------------
 (1,0,0,"",)
 (1,1,0,"",)
 (1,2,0,a,1)
(3 rows)

richard=> SELECT * FROM t WHERE v < (1,1,1,'',null);
      v      
-------------
 (1,0,0,"",)
 (1,1,0,"",)
(2 rows)