Where is using a table name as a "row value" documented?

Started by Gulyás Attilaover 2 years ago2 messagesdocs
Jump to latest
#1Gulyás Attila
toraritte@gmail.com

Hi,

I asked this on Stackoverflow[0]https://stackoverflow.com/questions/77191458/where-is-using-a-table-name-as-a-row-value-documented 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]https://stackoverflow.com/a/31035052/1498178
helped understand the logic of it, but wanted to understand this syntax.
Another answer[2]https://stackoverflow.com/a/69087309/1498178 states that "the reference to the table (alias) refers to
an existing row" and the[PostgreSQL `SELECT` documentation's `WHERE`
section[3]https://www.postgresql.org/docs/current/sql-select.html#SQL-WHERE 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]https://www.postgresql.org/search/?q=row%20value yielded references to
the 4.2 Value Expressions[5]https://www.postgresql.org/docs/current/sql-expressions.html page, but I couldn't find an answer there
either (unless I overlooked something).

Laurenz pointed it out[6]https://stackoverflow.com/a/77192003/1498178 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]https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS nor the pages referenced
there mention this usage anywhere (unless I missed it somehow). I also
found this answer[8]https://stackoverflow.com/a/21026085/1498178 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
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
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
[8]: https://stackoverflow.com/a/21026085/1498178

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Gulyás Attila (#1)
Re: Where is using a table name as a "row value" documented?

On Friday, December 8, 2023, Gulyás Attila <toraritte@gmail.com> wrote:

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.

I found this fairly,quickly when looking for what can be used as a column
reference. But I agree that this might need to be documented elsewhere as
well.

https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

David J.