StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
7461843
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
2
CommunityOwnedDate
CreationDate
2011-09-18T13:41:29.630
FavoriteCount
0
LastActivityDate
2016-12-23T09:34:56.427
LastEditDate
2016-12-23T09:34:56.427
LastEditorUserId
473181
OwnerUserId
947357
ParentId
7205878
PostTypeId
2
Score
39
ViewCount
0
LastEditorDisplayName
text
Body
A very simple example would be: <pre><code>> SELECT * FROM tab ORDER BY col USING < </code></pre> But this is boring, because this is nothing you can't get with the traditional <code>ORDER BY col ASC</code>. Also the standard catalog doesn't mention anything exciting about strange comparison functions/operators. You can get a list of them: <pre><code> > SELECT amoplefttype::regtype, amoprighttype::regtype, amopopr::regoper FROM pg_am JOIN pg_amop ON pg_am.oid = pg_amop.amopmethod WHERE amname = 'btree' AND amopstrategy IN (1,5); </code></pre> You will notice, that there are mostly <code><</code> and <code>></code> functions for primitive types like <code>integer</code>, <code>date</code> etc and some more for arrays and vectors and so on. None of these operators will help you to get a custom ordering. In most cases where custom ordering is required you can get away using something like <code>... ORDER BY somefunc(tablecolumn) ...</code> where <code>somefunc</code> maps the values appropriately. Because that works with every database this is also the most common way. For simple things you can even write an expression instead of a custom function. Switching gears up <code>ORDER BY ... USING</code> makes sense in several cases: <ul> <li>The ordering is so uncommon, that the <code>somefunc</code> trick doesn't work.</li> <li>You work with a non-primitive type (like <code>point</code>, <code>circle</code> or imaginary numbers) and you don't want to repeat yourself in your queries with strange calculations.</li> <li>The dataset you want to sort is so large, that support by an index is desired or even required.</li> </ul> I will focus on the complex datatypes: often there is more than one way to sort them in a reasonable way. A good example is <code>point</code>: You can "order" them by the distance to (0,0), or by x first, then by y or just by y or anything else you want. Of course, PostgreSQL has predefined operators for <code>point</code>: <pre><code> > CREATE TABLE p ( p point ); > SELECT p <-> point(0,0) FROM p; </code></pre> But none of them is declared usable for <code>ORDER BY</code> by default (see above): <pre><code> > SELECT * FROM p ORDER BY p; ERROR: could not identify an ordering operator for type point TIP: Use an explicit ordering operator or modify the query. </code></pre> Simple operators for <code>point</code> are the "below" and "above" operators <code><^</code> and <code>>^</code>. They compare simply the <code>y</code> part of the point. But: <pre><code> > SELECT * FROM p ORDER BY p USING >^; ERROR: operator > is not a valid ordering operator TIP: Ordering operators must be "<" or ">" members of __btree__ operator families. </code></pre> <code>ORDER BY USING</code> requires an operator with defined semantics: Obviously it must be a binary operator, it must accept the same type as arguments and it must return boolean. I think it must also be transitive (if a btree-index ordering. This explains the strange error messages containing the reference to btree. <code>ORDER BY USING</code> also requires not just one operator to be defined but an operator class and an operator family. While one could implement sorting with only one operator, PostgreSQL tries to sort efficiently and minimize comparisons. Therefore, several operators are used even when you specify only one - the others must adhere to certain mathematical constraints - I've already mentioned transitivity, but there are more. Switching Gears up Let's define something suitable: An operator for points which compares only the <code>y</code> part. The first step is to create a custom operator family which can be used by the btree index access method. <a href="http://www.postgresql.org/docs/9.1/interactive/xindex.html" rel="noreferrer">see</a> <pre><code> > CREATE OPERATOR FAMILY xyzfam USING btree; -- superuser access required! CREATE OPERATOR FAMILY </code></pre> Next we must provide a comparator function which returns -1, 0, +1 when comparing two points. This function WILL be called internally! <pre><code> > CREATE FUNCTION xyz_v_cmp(p1 point, p2 point) RETURNS int AS $$BEGIN RETURN btfloat8cmp(p1[1],p2[1]); END $$ LANGUAGE plpgsql; CREATE FUNCTION </code></pre> Next we define the operator class for the family. <a href="http://www.postgresql.org/docs/9.1/interactive/xindex.html" rel="noreferrer">See the manual</a> for an explanation of the numbers. <pre><code> > CREATE OPERATOR CLASS xyz_ops FOR TYPE point USING btree FAMILY xyzfam AS OPERATOR 1 <^ , OPERATOR 3 ?- , OPERATOR 5 >^ , FUNCTION 1 xyz_v_cmp(point, point) ; CREATE OPERATOR CLASS </code></pre> This step combines several operators and functions and also defines their relationship and meaning. For example <code>OPERATOR 1</code> means: This is the operator for <code>less-than</code> tests. Now the operators <code><^</code> and '>^' can be used in <code>ORDER BY USING</code>: <pre><code>> INSERT INTO p SELECT point(floor(random()*100), floor(random()*100)) FROM generate_series(1, 5); INSERT 0 5 > SELECT * FROM p ORDER BY p USING >^; p --------- (17,8) (74,57) (59,65) (0,87) (58,91) </code></pre> Voila - sorted by y. To sum it up: <code>ORDER BY ... USING</code> is an interesting look under the hood of PostgreSQL. But nothing you will require anytime soon unless you work in very specific areas of database technology. Another example can be found <a href="https://www.postgresql.org/docs/9.1/static/xindex.html#XINDEX-EXAMPLE" rel="noreferrer">in the Postgres docs.</a> with source code for the example <a href="https://github.com/postgres/postgres/blob/master/src/tutorial/complex.source" rel="noreferrer">here</a> and <a href="https://github.com/postgres/postgres/blob/master/src/tutorial/complex.c" rel="noreferrer">here</a>. This example also shows how to create the operators.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. PO"ORDER BY ... USING" clause in PostgreSQL
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USSimon
UserOwnerUserId
1. USA.H.
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. PO"ORDER BY ... USING" clause in PostgreSQL
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId
1. CO+1 Great answer!
 singulars
 PostPostId
 PO
 UserUserId
 USa_horse_with_no_name

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.