Server does not recognize a custom type literal

user_3560724 0
Viewed 46

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.

custom-type postgresql
2 Answers

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)
user_10294530

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
user_19957380
Related