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
