From: | Gulyás Attila <toraritte(at)gmail(dot)com> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Subject: | Where is using a table name as a "row value" documented? |
Date: | 2023-12-08 10:54:35 |
Message-ID: | CADnELpgYB+3JSYQ6J0FdBjwvWqXLRVZ4U+8NtzKv81X6rOWw-Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
Hi,
I asked this on Stackoverflow[0] months ago, and finally posting it here as
well, along with Laurenz Albe's helpful comments:
The query `SELECT * FROM table WHERE NOT (table IS NOT NULL);` finds all
the rows in `table` that have a `null` value in any column. This answer[1]
helped understand the logic of it, but wanted to understand this syntax.
Another answer[2] states that "the reference to the table (alias) refers to
an existing row" and the[PostgreSQL `SELECT` documentation's `WHERE`
section[3] states that "a row satisfies the condition if it returns true
when the actual **row values** are substituted for any variable references"
(emphasis mine). A keyword search on "row values"[4] yielded references to
the 4.2 Value Expressions[5] page, but I couldn't find an answer there
either (unless I overlooked something).
Laurenz pointed it out[6] that
> the technical term in PostgreSQL is a "whole-row reference",
> and it doesn't seem to be documented except in the source
> code. By using the table name as a column, you get a composite
> value consisting of all columns.
>
> You can see
>
> SELECT tab FROM tab;
>
> as being the same as
>
> SELECT ROW(tab.*) FROM tab;
>
> This is non-standard behavior (as is the use of * inside
> a ROW() constructor).
Unfortunately, neither the row constructor docs[7] nor the pages referenced
there mention this usage anywhere (unless I missed it somehow). I also
found this answer[8] helpful when trying to find the relevant docs.
Appreciatively,
Attila
[0]:
https://stackoverflow.com/questions/77191458/where-is-using-a-table-name-as-a-row-value-documented
[1]: https://stackoverflow.com/a/31035052/1498178
[2]: https://stackoverflow.com/a/69087309/1498178
[3]: https://www.postgresql.org/docs/current/sql-select.html#SQL-WHERE
[4]: https://www.postgresql.org/search/?q=row%20value
[5]: https://www.postgresql.org/docs/current/sql-expressions.html
[6]: https://stackoverflow.com/a/77192003/1498178
[7]:
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
[8]: https://stackoverflow.com/a/21026085/1498178
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2023-12-08 13:58:30 | Re: Where is using a table name as a "row value" documented? |
Previous Message | Zane Duffield | 2023-12-08 10:18:45 | Re: Inconsistent information on default ordering for ORDER BY clause |