Thread: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?

v12.13

https://www.postgresql.org/docs/12/sql-select.html

The docs say that one of these are required in the SELECT list.

[ * | expression [ [ AS ] output_name ] [, ...] ]

However, not mentioning anything also works, though acts like COUNT(*).

test=# select * from sales_detail;
 cust_id |        order_ts         | seq_no | inventory_id | quantity | price  | tax_rate
---------+-------------------------+--------+--------------+----------+--------+----------
       1 | 2020-01-05 13:05:42.567 |      1 |        12345 |     5.80 |  28.40 |    0.092
       1 | 2020-01-05 13:05:42.567 |      2 |        23456 |     6.00 |  98.40 |    0.092
       1 | 2020-01-05 13:05:42.567 |      3 |        34567 |     1.80 |  67.00 |    0.092
       1 | 2020-01-05 13:05:42.567 |      4 |        45678 |   450.00 |   2.00 |    0.092
       1 | 2020-02-05 13:05:42.567 |      1 |         6575 |     5.20 | 567.00 |    0.045
       1 | 2020-02-05 13:05:42.567 |      2 |         4565 |   456.00 | 545.00 |    0.045
       1 | 2020-02-05 13:05:42.567 |      3 |         7899 |   768.00 | 432.00 |    0.045
       1 | 2020-02-05 13:05:42.567 |      4 |         2354 |   556.00 | 890.00 |    0.045
       1 | 2020-03-05 13:05:42.567 |      1 |         6575 |     5.20 | 567.00 |    0.045
       1 | 2020-03-05 13:05:42.567 |      2 |         4565 |   456.00 | 545.00 |    0.045
       1 | 2020-03-05 13:05:42.567 |      3 |         7899 |   768.00 | 432.00 |    0.045
(11 rows)

test=#
test=# select  from sales_detail;
--
(11 rows)

Is it interpreting the lack of column names, expressions or "*" as a null expression?

We only noticed this when someone forgot to put an asterisk in a psql query and thought something was broken.  We expected psql to throw a syntax error...

 v12.13

https://www.postgresql.org/docs/12/sql-select.html

The docs say that one of these are required in the SELECT list.

[ * | expression [ [ AS ] output_name ] [, ...] ]

However, not mentioning anything also works, though acts like COUNT(*).

test=# select * from sales_detail;
 cust_id |        order_ts         | seq_no | inventory_id | quantity | price  | tax_rate
---------+-------------------------+--------+--------------+----------+--------+----------
       1 | 2020-01-05 13:05:42.567 |      1 |        12345 |     5.80 |  28.40 |    0.092
       1 | 2020-01-05 13:05:42.567 |      2 |        23456 |     6.00 |  98.40 |    0.092
       1 | 2020-01-05 13:05:42.567 |      3 |        34567 |     1.80 |  67.00 |    0.092
       1 | 2020-01-05 13:05:42.567 |      4 |        45678 |   450.00 |   2.00 |    0.092
       1 | 2020-02-05 13:05:42.567 |      1 |         6575 |     5.20 | 567.00 |    0.045
       1 | 2020-02-05 13:05:42.567 |      2 |         4565 |   456.00 | 545.00 |    0.045
       1 | 2020-02-05 13:05:42.567 |      3 |         7899 |   768.00 | 432.00 |    0.045
       1 | 2020-02-05 13:05:42.567 |      4 |         2354 |   556.00 | 890.00 |    0.045
       1 | 2020-03-05 13:05:42.567 |      1 |         6575 |     5.20 | 567.00 |    0.045
       1 | 2020-03-05 13:05:42.567 |      2 |         4565 |   456.00 | 545.00 |    0.045
       1 | 2020-03-05 13:05:42.567 |      3 |         7899 |   768.00 | 432.00 |    0.045
(11 rows)

test=#
test=# select  from sales_detail;
--
(11 rows)

Is it interpreting the lack of column names, expressions or "*" as a null expression?

We only noticed this when someone forgot to put an asterisk in a psql query and thought something was broken.  We expected psql to throw a syntax error...

--
Born in Arizona, moved to Babylonia.

On Wednesday, February 1, 2023, Ron <ronljohnsonjr@gmail.com> wrote:

test=# select  from sales_detail;
--
(11 rows)

Ron <ronljohnsonjr@gmail.com> writes:
> v12.13
> https://www.postgresql.org/docs/12/sql-select.html

> The docs say that one of these are required in the SELECT list.

> [ * |/|expression|/  [ [ AS ]/|output_name|/  ] [, ...] ]

Really?  I don't see anything to that effect in either the syntax
synopsis or the text, plus there's this in the Compatibility section:

    Empty SELECT Lists

    The list of output expressions after SELECT can be empty, producing a
    zero-column result table. This is not valid syntax according to the
    SQL standard. PostgreSQL allows it to be consistent with allowing
    zero-column tables. However, an empty list is not allowed when
    DISTINCT is used.

We probably did say that before 9.4 or so, but if you still find it
in modern releases, please say exactly where.

> However, *not* mentioning anything also works, though acts like COUNT(*).

Well, you get some number of zero-column rows.  psql's display of that is
a bit idiosyncratic, but nobody's bothered to improve it.

            regards, tom lane

On Wednesday, February 1, 2023, Ron <ronljohnsonjr@gmail.com> wrote:

https://www.postgresql.org/docs/12/sql-select.html

The docs say that one of these are required in the SELECT list.

[ * | expression [ [ AS ] output_name ] [, ...] ]