On 21/06/2019 15:36, Tom Lane wrote: > Ancoron Luciferis <ancoron.luciferis@xxxxxxxxxxxxxx> writes: >> I am creating a new operator class for version 1 UUID's with an >> extension and thought I was almost done by implementing everything >> including SortSupport and creating a new opclass as follows: > >> CREATE OPERATOR CLASS uuid_timestamp_ops FOR TYPE uuid >> USING btree AS >> OPERATOR 1 <*, >> ... > >> ...but when sorting on an (unique) index column, I still get a separate >> sort, not using the index, e.g.: > > You did not show your test query, but I imagine it just asked for the > type's ordinary sort order, which is not what this opclass is claiming > to provide. To rely on the index's sort order you'd need something like > > select id from uuid_v1_ext > where id <* '2b55fb04-33d8-11e9-9cfa-e03f494ffcf7' > order by id using <* ; > > If you want this opclass to become the default sort order for uuid > you'd have to remove the opcdefault marking from uuid_ops and attach > it to this opclass instead. No, I'm not sure that that wouldn't have > unpleasant side-effects. > > regards, tom lane > OK, I somehow feared I'd be getting such an answer. But thanks a lot for the "using <*" trick, which I somehow didn't know even exists. That solves the problem for me at least. Because of this issue I was already thinking about creating a new data type which is basically just a new name for the existing "uuid" but would enforce version 1, for which I then could provide the opclass as default, or? Btw. is there some example how to create derived types for PG properly without copy/paste of a lot of existing code? Thanx a lot and cheers, Ancoron
