Re: [SQL] ordering operator for bytea

Michael McCarthy <michael(at)tcsi(dot)com> writes:
> Given the following schema and query (to PQexec) we get an error from
> postgres (postmaster debug log shown):
> query: DECLARE osp_cursor CURSOR FOR select item_index, octet_string,
> parent_oid_inst_ms, parent_oid_inst_ls from asn1octtable15545 where
> (parent_oid_class = 5000 and attr_code = 5023 and parent_oid_tomid =
> 15545 and parent_oid_inst_ms = 1018757128 and parent_oid_inst_ls =
> 948163998) or (parent_oid_class = 5000 and attr_code = 5023 and
> parent_oid_tomid = 15545 and parent_oid_inst_ms = 1018757127 and
> parent_oid_inst_ls = 948163998) order by parent_oid_inst_ms ASC,
> parent_oid_inst_ls ASC, item_index ASC
> ERROR: Unable to identify an ordering operator '<' for type 'bytea'
> Use an explicit ordering operator or modify the query

> Strangely, we tried the same query in psql, and it works fine;

Are you by chance running with KSQO enabled in your application?

Your query looks to me like the kind that KSQO would trigger on;
and if it triggers, it transforms the query into a UNION. UNION
requires a DISTINCT pass, which requires sorting, which requires
an ordering operator --- and bytea hasn't got one.

There isn't any real good reason for bytea not to have comparison
operators, AFAIK ... it's just that no one has gotten 'round to
writing them. If you have a strong need to have KSQO turned on,
I'd suggest writing up some comparators for bytea using memcmp.
(Please contribute them if you do ;-).)

> only seen this problem for tables that contain a bytea column, but we need
> to be able to store unprintable characters, and to order rows from such a
> table.

text shouldn't have any problem with "unprintable" characters other than
null (\0); if you can live without storing nulls, switching to text
might be the path of least resistance.

regards, tom lane