I highly recommend puzzling through some of these on your own first (it's a great way to build intuition for SQL's craziness), but here's a few explanations if you're stuck.

    jamie=# select a+1 from nums group by a+1;
     ?column?
    ----------
            2
            4
            1
            3
    (4 rows)
    
    jamie=# select a+1 from nums group by 1+a;
    ERROR:  column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function
    LINE 1: select a+1 from nums group by 1+a;

In general, a query like `SELECT a FROM t GROUP BY a` is valid, while a query `SELECT b FROM t GROUP BY a` is not valid. Because you didn't group by column b, there are multiple values of b within each group, and it's not clear how to combine them. What you usually meant is something like

    SELECT a, sum(b) FROM t GROUP BY a

where you can see the sum of all b's for each distinct value of a.

The error message explains it really nicely: if there is a GROUP BY clause, every column in a SELECT list must either be used in the GROUP BY clause or appear in an aggregate function. This is kind of annoying, though; what if you want to group by a value derived from a column, like `a + 1`? (More realistically, maybe you want to trim whitespace or something.) I think the SQL standard would have you write

    SELECT a1 FROM (SELECT a + 1 AS a1 FROM t) _ GROUP BY a1

but that's annoying. PostgreSQL (and maybe other SQL implementations; not sure!) has some smarts about detecting when an expression is used verbatim in a SQL query, so you can just write

    SELECT a+1 FROM t GROUP BY a+1

and it will notice that the "a+1" in the SELECT list is literally the same as the "a+1" in the GROUP BY clause. But it's not that smart, because it doesn'that realize that "a+1" and "1+a" are semantically equivalent, and so this is an error:

    SELECT a+1 FROM t GROUP BY 1+a

----

    jamie=# select b from nums group by a;
     b
    ---
    (0 rows)
    
    jamie=# select a from nums group by b;
    ERROR:  column "nums.a" must appear in the GROUP BY clause or be used in an aggregate function
    LINE 1: select a from nums group by b;

An extension of the previous example, where PostgreSQL can infer that when grouping by a all b's are unique. It can't infer the converse. The reason is that a is the primary key and so guaranteed to be unique, and therefore `GROUP BY a` is meaningless and can be elided.

(This one really confused me for a while. I only learned this recently.)

----

    jamie=# select a, b from nums order by 2;
    a |  b
    ---+-----
     3 | bar
     0 | foo
     1 | foo
     2 | foo
    (4 rows)
    
    jamie=# select a, b from nums order by 1+1;
     a |  b
    ---+-----
     0 | foo
     1 | foo
     2 | foo
     3 | bar
    (4 rows)

`ORDER BY <n>` means "order by the Nth column", but `ORDER BY <expr>` means "order by the value of <expr>`. So you get the goofy behavior that `ORDER BY 2` in the above query means order by column b, but `ORDER BY 1 + 1` means order by the literal value "2", i.e., don't order by anything useful at all. (It just so happens that you get primary key order in the example above, but that's not guaranteed by any means.)

----

Most of the others have a similar feel. Let me know if you're particularly puzzled by any others.