零列查询的用途：是PostgreSQL语法扩展还是有实际应用场景？

Great question! Let's unpack why PostgreSQL allows selecting zero columns, and when you might actually use this feature.

First, to clarify: This is indeed a PostgreSQL-specific syntax extension—the SQL standard requires at least one expression after the SELECT keyword. PostgreSQL relaxes this rule to offer more flexibility, treating a zero-column query as returning a set of empty rows (each with no columns).

Practical Use Cases #

While it might seem odd at first, there are real scenarios where this comes in handy:

• Validate table existence/access permissions: A quick SELECT FROM your_table; will either return empty rows (if the table exists and you have SELECT access) or throw an error (if the table doesn't exist or you lack permissions). It's a lightweight check without needing to fetch actual column data.
• Execute CTEs without returning data: If you're using a CTE to run write operations (like UPDATE, INSERT, or DELETE) and only care about confirming the operation ran (via the number of rows returned), a zero-column main query avoids redundant column output. For example:

  WITH user_updates AS (
    UPDATE users 
    SET last_active = NOW() 
    WHERE last_active < '2023-01-01'::DATE
    RETURNING *
  )
  SELECT FROM user_updates;
  

  This runs the update and returns the number of rows modified, without cluttering results with all the RETURNING columns.
• Generate empty row sequences: You can use zero-column queries with set-generating functions to create empty rows for testing or scaffolding. For example, SELECT FROM generate_series(1, 3); returns 3 empty rows, which is useful if you need to create a fixed number of rows without any specific column values.

Syntax Context #

PostgreSQL's support for zero-column queries aligns with its philosophy of flexible, user-centric syntax. It treats an empty SELECT clause as a valid way to request rows without any column data, which can be cleaner than using a dummy column (like SELECT 1 FROM ...) when you don't need actual values.

It's worth noting that SELECT FROM your_table; is not the same as SELECT * FROM your_table;—the former returns rows with no columns, while the latter returns all columns in the table.

内容的提问来源于stack exchange，提问作者Lukasz Szozda