Join condition parsing puzzle

[Mark Jeffcoat]

I'm looking at a query generated by SQLAlchemy. It works; Postgres is
perfectly happy to run it, and it gives answers that make sense to the
guy who wrote it. But I don't understand why it works.

Stripped way down ...

CREATE VIEW relation_a (id_c, id_v)
AS VALUES (1, 20),  (2, 21), (3, 22);

CREATE VIEW relation_b (id_c, id_v, id_p)
AS VALUES (1, 20, 300), (2, 21, 301);

CREATE VIEW relation_c (id_p)
AS VALUES (301);

SELECT *
FROM relation_a
LEFT JOIN relation_b
JOIN relation_c
ON (relation_c.id_p = relation_b.id_p)
ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = relation_b.id_v);


I would have claimed before seeing this example that it wasn't even
grammatical; I thought the only legal place to write the ON clause was
immediately after the JOIN. Apparently not.

How should I read this query?  I'd appreciate any help understanding this.

-- 
Mark Jeffcoat
Austin, TX

Re: Join condition parsing puzzle

[Mark Jeffcoat]

On Thu, Aug 23, 2018 at 4:51 PM, Tom Lane  wrote:
>
> Mark Jeffcoat  writes:

> > I would have claimed before seeing this example that it wasn't even
> > grammatical; I thought the only legal place to write the ON clause was
> > immediately after the JOIN. Apparently not.
>
> You read it as
>
> SELECT *
> FROM
>   relation_a
>   LEFT JOIN (relation_b
>  JOIN relation_c
>  ON (relation_c.id_p = relation_b.id_p))
>   ON (relation_a.id_c = relation_b.id_c AND relation_a.id_v = 
> relation_b.id_v);
>
> There's no other valid way to parenthesize it, so that's what
> the parser does.



Thank you very much for your help, Tom. In retrospect, I see I'd
over-generalized the rule that sub-selects in the from clause require
an alias.

Clear now.

-- 
Mark Jeffcoat
Austin, TX

Showing table comments with psql

[Mark Jeffcoat]

I'm creating table and view comments with "COMMENT ON", and I can find
the comment in pg_description, but I can't find a way to show the
table comments using psql.

$ psql --version
psql (PostgreSQL) 11.1 (Debian 11.1-1+b2)

I'd expect to see table comments by using \d+, and found an old post
on this list where \dd worked; neither work for me today. Am I looking
in the right place? Is this a regression?

Thank you.


postgres=# create table test_table (col1 integer);
CREATE TABLE

postgres=# comment on table test_table is 'this is a table comment';
COMMENT

postgres=# \d+ test_table
Table "public.test_table"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats
target | Description
+-+---+--+-+-+--+-
 col1   | integer |   |  | | plain   |  |

postgres=# \dd test_table
 Object descriptions
 Schema | Name | Object | Description
+--++-
(0 rows)

postgres=# select * from pg_description where objoid = (select oid
from pg_class where relname = 'test_table');
 objoid | classoid | objsubid |   description
+--+--+-
 714760 | 1259 |    0 | this is a table comment
(1 row)

-- 
Mark Jeffcoat
Austin, TX

Re: Showing table comments with psql

[Mark Jeffcoat]

On Fri, Jan 4, 2019 at 11:00 AM Pavel Stehule  wrote:

> postgres=#  create table test_table (col1 integer);
> CREATE TABLE
> postgres=# comment on table test_table is 'this is a table comment';
> COMMENT
> postgres=# \dt+
>  List of relations
> ┌┬┬───┬───┬─┬─┐
> │ Schema │Name│ Type  │ Owner │  Size   │   Description   │
> ╞╪╪═══╪═══╪═╪═╡
> │ public │ test_table │ table │ pavel │ 0 bytes │ this is a table comment │
> └┴┴───┴───┴─┴─┘
> (1 row)
>

I was so close! That works; thank you very much for your help, Pavel.

-- 
Mark Jeffcoat
Austin, TX