Question 254042 in PostgreSQL Question posted in
The official documentation can be found here.

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

SELECT * FROM table WHERE NOT (table IS NOT NULL);

finds all the rows in table that have a null value in any column. The logic is clear (see answer), but want to understand this syntax. A statement in an answer says "the reference to the table (alias) refers to an existing row" and the PostgreSQL SELECT documentation’s WHERE section 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" yielded references to the 4.2 Value Expressions page, but I couldn’t find an answer there either (unless I overlooked something).

Tags:

2

Answers


  1. 0

    A variation on the answer from here could be (all of the code below is available on the fiddle here):

    WITH cte AS
(
  SELECT ROW(t.*)::TEXT FROM t
)
SELECT * FROM cte
WHERE row ~ '(,|,,|,)';

    Now, it uses a regex and these can be expensive – as we can see from the `EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS) <My_Query>’ – my regex query is about 3 times as expensive as

    SELECT * FROM t WHERE NOT (t IS NOT NULL);

    I also included a couple of other variants of using the ROW keyword in the fiddle.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search