Re: TABLE not synonymous with SELECT * FROM?

Hi,

According to http://www.postgresql.org/docs/9.3/static/sql-select.html#SQL-TABLE

"

The command

TABLE name

is completely equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax
variant in parts of complex queries.

"

However, this isn't true:

colin(at)corundum:~$ psql
psql (9.4devel, server 9.3.1)
Type "help" for help.

eyedb=# table x;
a
---
1
(1 row)

eyedb=# table x limit 10;
a
---
1
(1 row)

eyedb=# table x where a = 1;
ERROR: syntax error at or near "where"
LINE 1: table x where a = 1;
^
eyedb=#

I would've thought it was implemented as a shortcut for "SELECT *
FROM" at the parse level (ie encounter "TABLE" and insert "SELECT *
FROM" into the parse tree and continue), but it seems there is more to
it.

Is the documentation wrong? Or is something broken?

Cheers,

Colin

"Colin 't Hart" <colin(at)sharpheart(dot)org> writes:
> I would've thought it was implemented as a shortcut for "SELECT *
> FROM" at the parse level (ie encounter "TABLE" and insert "SELECT *
> FROM" into the parse tree and continue), but it seems there is more to
> it.

If you look at the PG grammar you'll see that "TABLE relation_expr"
appears as one variant of simple_select, which means that you can attach
WITH, ORDER BY, FOR UPDATE, or LIMIT to it. The other things you mention
are only possible in a clause that actually starts with SELECT. AFAICS,
this comports with the SQL standard's syntax specification (look at the
difference between <query specification> and <query expression>).
The comment for simple_select saith

* Note that sort clauses cannot be included at this level --- SQL requires
* SELECT foo UNION SELECT bar ORDER BY baz
* to be parsed as
* (SELECT foo UNION SELECT bar) ORDER BY baz
* not
* SELECT foo UNION (SELECT bar ORDER BY baz)
* Likewise for WITH, FOR UPDATE and LIMIT. Therefore, those clauses are
* described as part of the select_no_parens production, not simple_select.
* This does not limit functionality, because you can reintroduce these
* clauses inside parentheses.

regards, tom lane

On 11 November 2013 15:03, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Colin 't Hart" <colin(at)sharpheart(dot)org> writes:
>> I would've thought it was implemented as a shortcut for "SELECT *
>> FROM" at the parse level (ie encounter "TABLE" and insert "SELECT *
>> FROM" into the parse tree and continue), but it seems there is more to
>> it.
>
> If you look at the PG grammar you'll see that "TABLE relation_expr"
> appears as one variant of simple_select, which means that you can attach
> WITH, ORDER BY, FOR UPDATE, or LIMIT to it. The other things you mention
> are only possible in a clause that actually starts with SELECT. AFAICS,
> this comports with the SQL standard's syntax specification (look at the
> difference between <query specification> and <query expression>).
> The comment for simple_select saith
>
> * Note that sort clauses cannot be included at this level --- SQL requires
> * SELECT foo UNION SELECT bar ORDER BY baz
> * to be parsed as
> * (SELECT foo UNION SELECT bar) ORDER BY baz
> * not
> * SELECT foo UNION (SELECT bar ORDER BY baz)
> * Likewise for WITH, FOR UPDATE and LIMIT. Therefore, those clauses are
> * described as part of the select_no_parens production, not simple_select.
> * This does not limit functionality, because you can reintroduce these
> * clauses inside parentheses.

Makes sense. I had been wondering about that order by stuff too.

Methinks we should fix the documentation, something like:

The command

TABLE name

is equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax
variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT,
and Locking clauses and set operations can be used with TABLE; the
WHERE and ORDER BY clauses and any form of aggregation cannot be used.

Cheers,

Colin

Colin 't Hart wrote
> Methinks we should fix the documentation, something like:
>
> The command
>
> TABLE name
>
> is equivalent to
>
> SELECT * FROM name
>
> It can be used as a top-level command or as a space-saving syntax
> variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT,
> and Locking clauses and set operations can be used with TABLE; the
> WHERE and ORDER BY clauses and any form of aggregation cannot be used.

The paragraph is unnecessary if the Synopsis section of the SELECT
documentation is updated to correctly reflect all the valid clauses that can
be attached to TABLE. The current reading implies that you cannot attach
anything so when you said LIMIT worked I was surprised.

Also, testing seems to confirm that the allowance of LIMIT implies that
OFFSET is allowed as well.

If TABLE is allowed as a top-level command why doesn't it get its own page
in the SQL commands section? It really doesn't matter - and honestly while
I've known about it I've never actually thought to use it in actual queries
because as soon as you want to do something special you have to switch it
out for SELECT * FROM anyway - but it does seem inconsistent.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/TABLE-not-synonymous-with-SELECT-FROM-tp5777695p5777733.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

David Johnston wrote
> The paragraph is unnecessary if the Synopsis section of the SELECT
> documentation is updated to correctly reflect all the valid clauses that
> can be attached to TABLE. The current reading implies that you cannot
> attach anything so when you said LIMIT worked I was surprised.
>
> Also, testing seems to confirm that the allowance of LIMIT implies that
> OFFSET is allowed as well.
>
> If TABLE is allowed as a top-level command why doesn't it get its own page
> in the SQL commands section? It really doesn't matter - and honestly
> while I've known about it I've never actually thought to use it in actual
> queries because as soon as you want to do something special you have to
> switch it out for SELECT * FROM anyway - but it does seem inconsistent.

I'd be in favour of the first. Since it's sort-of synonymous for SELECT *
FROM it would make sense to include it on the same page to avoid having to
explain the same clauses again. But then it should be listed earlier, before
the clauses which can be used with it.

Yes, all LIMIT, OFFSET, FETCH stuff works. Also very useful in a WITH:

WITH x AS (
TABLE foo
ORDER BY colX DESC
LIMIT 10
)
...;

Note that set operations work too, and that's how I most often use it for
testing rewritten queries:
create table x as <original_query>;
create table y as <rewritten_query>;
table x except table y;
table y except table x;

Cheers,

Colin

--
View this message in context: http://postgresql.1045698.n5.nabble.com/TABLE-not-synonymous-with-SELECT-FROM-tp5777695p5777883.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

David et al,

How about something like this?

Cheers,

Colin

On Wed, Nov 13, 2013 at 10:28:07AM +0100, Colin 't Hart wrote:
> David et al,
>
> How about something like this?

I have applied a modified version of your patch. I didn't like the
idea of putting "SELECT" inside an OR syntax clauses --- the syntax is
already too complicated. I also didn't like moving the TABLE mention up
in the file. What I did do was to document the supported TABLE clauses,
and add some of your verbiage. Thanks.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +