Thread: row-wise comparison question/issue
I noticed something odd when trying to use the row-wise comparison mentioned in the release notes for 8.2 and in the docs http://developer.postgresql.org/pgdocs/postgres/functions-comparisons.html#ROW-WISE-COMPARISON This sets up a suitable test: create type myrowtype AS (a integer, b integer); create table myrowtypetable (rowval myrowtype); insert into myrowtypetable select (a, b)::myrowtype from generate_series(1,5) a, generate_series(1,5) b; First I get this error: select rowval < rowval from myrowtypetable ; ERROR: operator does not exist: myrowtype < myrowtype LINE 1: select rowval < rowval from myrowtypetable ; ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. OK, I guess I can live with that. I did create a new type, and there are no operators for it... Now, I can do the following (pointless) query select ROW((rowval).*) < ROW((rowval).*) from myrowtypetable ; and I get 25 rows of 'f'. So far so good. But if I try to do select rowval from myrowtypetable ORDER BY ROW((rowval).*); ERROR: could not identify an ordering operator for type record HINT: Use an explicit ordering operator or modify the query. or even select rowval from myrowtypetable ORDER BY ROW((rowval).*) USING <; ERROR: operator does not exist: record < record HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. I know that that less-than operator exists, because I just used it in the query that worked above. It seems that ORDER BY just can't find it for some reason. Is it supposed to not work in order by? That doesn't really make sense to me why order by should be special for this. -- All extremists should be taken out and shot.
On 10/20/06, Jeremy Drake <pgsql@jdrake.com> wrote: > I noticed something odd when trying to use the row-wise comparison > mentioned in the release notes for 8.2 and in the docs > http://developer.postgresql.org/pgdocs/postgres/functions-comparisons.html#ROW-WISE-COMPARISON > > This sets up a suitable test: > > create type myrowtype AS (a integer, b integer); > create table myrowtypetable (rowval myrowtype); > > insert into myrowtypetable select (a, b)::myrowtype from > generate_series(1,5) a, generate_series(1,5) b; > > First I get this error: > > select rowval < rowval from myrowtypetable ; > ERROR: operator does not exist: myrowtype < myrowtype > LINE 1: select rowval < rowval from myrowtypetable ; > ^ > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > > OK, I guess I can live with that. I did create a new type, and there are > no operators for it... > > Now, I can do the following (pointless) query > select ROW((rowval).*) < ROW((rowval).*) from myrowtypetable ; > > and I get 25 rows of 'f'. So far so good. > > But if I try to do > select rowval from myrowtypetable ORDER BY ROW((rowval).*); > ERROR: could not identify an ordering operator for type record > HINT: Use an explicit ordering operator or modify the query. > > or even > select rowval from myrowtypetable ORDER BY ROW((rowval).*) USING <; > ERROR: operator does not exist: record < record > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > > I know that that less-than operator exists, because I just used it in the > query that worked above. It seems that ORDER BY just can't find it for > some reason. > > Is it supposed to not work in order by? That doesn't really make sense to > me why order by should be special for this. that would be neat. i know that row construction and comparison as currently implemented is sql standard...is the stuff you are suggesting also standard? (im guessing no). I'll throw something else on the pile: esilo=# select (foo).* from foo order by (foo).*; ERROR: column foo.* does not exist esilo=# select (foo).* from foo;a | b | c ---+---+--- (0 rows) seems a little contradictory... note jeremy that the more common use of row comparison would be to construct rows on the fly, usually on fields comprising a key with an explicit order by: select a,b,c from foo where (a,b,c) > (1,2,3) order by a,b,c; works fine merlin
Jeremy Drake <pgsql@jdrake.com> writes:
> select rowval from myrowtypetable ORDER BY ROW((rowval).*) USING <;
> ERROR: operator does not exist: record < record
This isn't required by the spec, and it's not implemented. I don't
see that it'd give any new functionality anyway, since you can always
do ORDER BY rowval.f1, rowval.f2, ...
The cases that are implemented are comparisons of explicit row
constructors, eg "(a,b,c) < (d,e,f)" --- which I think is all
you'll find support for in the spec.
regards, tom lane
On Fri, 20 Oct 2006, Tom Lane wrote: > Jeremy Drake <pgsql@jdrake.com> writes: > > select rowval from myrowtypetable ORDER BY ROW((rowval).*) USING <; > > ERROR: operator does not exist: record < record > > This isn't required by the spec, and it's not implemented. I don't > see that it'd give any new functionality anyway, since you can always > do ORDER BY rowval.f1, rowval.f2, ... > > The cases that are implemented are comparisons of explicit row > constructors, eg "(a,b,c) < (d,e,f)" --- which I think is all > you'll find support for in the spec. I just think it is quite unexpected that the operator < is defined in some places and not in others. And the way I wrote the order by, it should have been comparing explicit row constructors (compare the explicitly constructed row for each rowval in order to sort). I don't understand how the operator < in a where clause would be different than the operator < used by the order by. If I were to make a custom type in C, and write these same operators for it, they would work in both places, right? Why then would this be any different? -- If someone had told me I would be Pope one day, I would have studied harder. -- Pope John Paul I
Jeremy Drake <pgsql@jdrake.com> writes:
> I just think it is quite unexpected that the operator < is defined in some
> places and not in others.
Row-wise comparison isn't an operator, it's a syntactic construct.
Consider
(now(), 'foo', 42) < (SELECT timestampcol, textcol, intcol FROM sometable WHERE ...)
There isn't any single operator in the system that implements that.
(And no, orthogonality is not one of the strong points of SQL...)
regards, tom lane