Home > Blockchain >  Server does not recognize a custom type literal
Server does not recognize a custom type literal

Time:11-16

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)
  • Related