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.
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