Postgres Pro
Downloads
Home   >   mailing lists

Thread: order by

order by

From
Luca Ferrari
Date:
Hi all,
this could be trivial, but I found as an accident the following:

pgbench=> create table t as select v from generate_series( 1, 2 ) v;
SELECT 2
pgbench=> select * from t order by foo;
ERROR:  column "foo" does not exist
LINE 1: select * from t order by foo;
                                 ^
pgbench=> select * from t order by t;
 v
---
 1
 2
(2 rows)

The ORDER BY rejects non existent columns (right) but accepts the
table itself as an ordering expression.
Reading here <https://www.postgresql.org/docs/12/sql-select.html#SQL-ORDERBY>
I cannot really understand why it is working and which kind of
ordering it is applying at all, I suspect the same ordering as without
ORDER BY at all.

Any hint?

Just for the record, seems that supplying another table name is
rejected too, as I would expect:

pgbench=> select * from t order by pgbench_accounts;
ERROR:  column "pgbench_accounts" does not exist
LINE 1: select * from t order by pgbench_accounts;


Thanks,
Luca

Re: order by

From
Vijaykumar Jain
Date:
> Any hint?

you can run an explain analyze to check what is going on,
when you provide a table in query in the order by clause, it is
ordered by cols of that table in that order.

create table t(id int, value int);

postgres=# explain (analyze,verbose) select * from t order by t;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Sort  (cost=158.51..164.16 rows=2260 width=40) (actual
time=0.005..0.006 rows=0 loops=1)
   Output: id, value, t.*
   Sort Key: t.*
   Sort Method: quicksort  Memory: 25kB
   ->  Seq Scan on public.t  (cost=0.00..32.60 rows=2260 width=40)
(actual time=0.002..0.002 rows=0 loops=1)
         Output: id, value, t.*
 Planning Time: 0.033 ms
 Execution Time: 0.044 ms
(8 rows)

postgres=# truncate table t;
TRUNCATE TABLE
postgres=# insert into t values (100, 1);
INSERT 0 1
postgres=# insert into t values (50, 2);
INSERT 0 1
postgres=# select * from t order by t;
 id  | value
-----+-------
  50 |     2
 100 |     1
(2 rows)

postgres=# select * from t order by t.id, t.value;
 id  | value
-----+-------
  50 |     2
 100 |     1
(2 rows)

Re: order by

From
Luca Ferrari
Date:
On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain
<vijaykumarjain.github@gmail.com> wrote:
> you can run an explain analyze to check what is going on,
> when you provide a table in query in the order by clause, it is
> ordered by cols of that table in that order.

Clever, thanks!
I also realized that this "table to tuples" expansion works for GROUP BY too.
However, I'm not able to find this documented in GROUP BY, WHERE,
ORDER BY clauses sections into select documentation
<fgrouphttps://www.postgresql.org/docs/12/sql-select.html>. Could be
my fault, of course.

Luca

Re: order by

From
Thomas Munro
Date:
On Thu, Jun 10, 2021 at 8:40 PM Luca Ferrari <fluca1978@gmail.com> wrote:
> On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain
> > when you provide a table in query in the order by clause, it is
> > ordered by cols of that table in that order.
>
> Clever, thanks!
> I also realized that this "table to tuples" expansion works for GROUP BY too.
> However, I'm not able to find this documented in GROUP BY, WHERE,
> ORDER BY clauses sections into select documentation
> <fgrouphttps://www.postgresql.org/docs/12/sql-select.html>. Could be
> my fault, of course.

There's something about this here:

https://www.postgresql.org/docs/13/rowtypes.html#ROWTYPES-USAGE

Re: order by

From
Laurenz Albe
Date:
On Thu, 2021-06-10 at 10:39 +0200, Luca Ferrari wrote:
> I also realized that this "table to tuples" expansion works for GROUP BY too.
> However, I'm not able to find this documented in GROUP BY, WHERE,
> ORDER BY clauses sections into select documentation
> <fgrouphttps://www.postgresql.org/docs/12/sql-select.html>;. Could be
> my fault, of course.

I don't think it is really documented outside the source, but it is
called a "whole-row reference" and behaves in SQL like composite
data type that belongs to the table.

So ordering is lexicographical, equality is equality of all members,
and don't ask me about IS NULL and IS NOT NULL, else I point you to
https://www.postgresql.org/message-id/flat/48BDABE9-88AB-46E9-BABE-F70DDBFB98BD%40kineticode.com

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: order by

From
Tom Lane
Date:
Luca Ferrari <fluca1978@gmail.com> writes:
> The ORDER BY rejects non existent columns (right) but accepts the
> table itself as an ordering expression.

As others have noted, this is basically taking the table name as a
whole-row variable, and then sorting per the rules for composite
types.  I write to point out that you can often get some insight into
what the parser thought it was doing by examining the reverse-listing
for the query.  The simplest way to do that is to create a view and
examine the view:

regression=# create view v as  
regression-# select * from t order by t;
CREATE VIEW
regression=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description 
--------+---------+-----------+----------+---------+---------+-------------
 v      | integer |           |          |         | plain   | 
View definition:
 SELECT t.v
   FROM t
  ORDER BY t.*;

The form "t.*" is a more explicit way to write a whole-row variable.

(IIRC, accepting it without "*" is a PostQUEL-ism that we've never
got rid of.  I think that with "*", there's at least some support
for the concept in the SQL standard.  But I'm insufficiently
caffeinated to want to go digging for that.)

            regards, tom lane