pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed"SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?
5+ messages / 4 participants
[nested] [flat]
* "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?
@ 2023-02-01 20:00 Ron <ronljohnsonjr@gmail.com>
2023-02-01 20:06 ` Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Rodrigo Luna <rodrigomrluna78@gmail.com>
2023-02-01 20:06 ` Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? David G. Johnston <david.g.johnston@gmail.com>
2023-02-01 20:10 ` Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Tom Lane <tgl@sss.pgh.pa.us>
2023-02-01 20:18 ` Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: Ron @ 2023-02-01 20:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?
2023-02-01 20:00 "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Ron <ronljohnsonjr@gmail.com>
@ 2023-02-01 20:06 ` Rodrigo Luna <rodrigomrluna78@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Rodrigo Luna @ 2023-02-01 20:06 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
SELECT
COUNT(*)
FROM
table_name
WHERE
condition;
Rodrigo Dev
Visite: https://rodrigoluna.net.br
YouTube: YouTube.com/@gamesbrs
> Em 1 de fev. de 2023, à(s) 17:00, Ron <ronljohnsonjr@gmail.com> escreveu:
>
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?
2023-02-01 20:00 "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Ron <ronljohnsonjr@gmail.com>
@ 2023-02-01 20:06 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-02-01 20:06 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wednesday, February 1, 2023, Ron <ronljohnsonjr@gmail.com> wrote:
>
> test=# select from sales_detail;
> --
> (11 rows)
>
It returned 11 rows as per the psql output text, if it acted like count(*),
an aggregate, it would have only returned one row.
You have produced an 11-row, 0-column, output table. It isn’t “supported”
but is allowed for technical convenience, hence the decision to avoid
trying to communicate this fact in the syntax diagram.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?
2023-02-01 20:00 "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Ron <ronljohnsonjr@gmail.com>
@ 2023-02-01 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-02-01 20:10 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?
2023-02-01 20:00 "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Ron <ronljohnsonjr@gmail.com>
@ 2023-02-01 20:18 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-02-01 20:18 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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* ] [, ...] ]
>
>
The square brackets you show are how optional elements are introduced so
while those two options exists neither are required.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-02-01 20:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-01 20:00 "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"? Ron <ronljohnsonjr@gmail.com>
2023-02-01 20:06 ` Rodrigo Luna <rodrigomrluna78@gmail.com>
2023-02-01 20:06 ` David G. Johnston <david.g.johnston@gmail.com>
2023-02-01 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-01 20:18 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox