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@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"Colin 't Hart" <colin@sharpheart.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11 November 2013 15:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Colin 't Hart" <colin@sharpheart.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
David et al,
How about something like this?
Cheers,
Colin
Attachments:
select.sgml.difftext/plain; charset=US-ASCII; name=select.sgml.diffDownload
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index e603b76..a68014b 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -33,13 +33,14 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
-SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
+{ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
* | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]
[ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
[ WHERE <replaceable class="parameter">condition</replaceable> ]
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
[ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
[ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
+| TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] }
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ]
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
@@ -60,8 +61,6 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
<replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
-
-TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -198,6 +197,27 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
<literal>UPDATE</literal> privilege as well (for at least one column
of each table so selected).
</para>
+
+ <refsect2 id="SQL-TABLE">
+ <title><literal>TABLE</literal> Command</title>
+
+ <para>
+ The command
+<programlisting>
+TABLE <replaceable class="parameter">name</replaceable>
+</programlisting>
+ is equivalent to
+<programlisting>
+SELECT * FROM <replaceable class="parameter">name</replaceable>
+</programlisting>
+ It can be used as a top-level command or as a space-saving syntax
+ variant in parts of complex queries. Only the <literal>WITH</>, <literal>ORDER BY</>, <literal>LIMIT</>,
+ and Locking clauses and set operations can be used with <command>TABLE</>; the
+ <literal>WHERE</> clause and any form of aggregation cannot be used.
+
+ Note that on this page and other places in the documentation, where <command>SELECT</> is mentioned, <command>TABLE</> is also assumed, subject to the restrictions mentioned here.
+ </para>
+ </refsect2>
</refsect1>
<refsect1>
@@ -211,7 +231,7 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
subqueries that can be referenced by name in the primary query.
The subqueries effectively act as temporary tables or views
for the duration of the primary query.
- Each subquery can be a <command>SELECT</command>, <command>VALUES</command>,
+ Each subquery can be a <command>SELECT</command>, <command>TABLE</>, <command>VALUES</command>,
<command>INSERT</command>, <command>UPDATE</command> or
<command>DELETE</command> statement.
When writing a data-modifying statement (<command>INSERT</command>,
@@ -1437,23 +1457,6 @@ SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss ORDER BY column1;
</para>
</caution>
</refsect2>
-
- <refsect2 id="SQL-TABLE">
- <title><literal>TABLE</literal> Command</title>
-
- <para>
- The command
-<programlisting>
-TABLE <replaceable class="parameter">name</replaceable>
-</programlisting>
- is completely equivalent to
-<programlisting>
-SELECT * FROM <replaceable class="parameter">name</replaceable>
-</programlisting>
- It can be used as a top-level command or as a space-saving syntax
- variant in parts of complex queries.
- </para>
- </refsect2>
</refsect1>
<refsect1>
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Attachments:
select.difftext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
new file mode 100644
index 7395754..f1bc158
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*************** TABLE [ ONLY ] <replaceable class="param
*** 214,220 ****
subqueries that can be referenced by name in the primary query.
The subqueries effectively act as temporary tables or views
for the duration of the primary query.
! Each subquery can be a <command>SELECT</command>, <command>VALUES</command>,
<command>INSERT</command>, <command>UPDATE</command> or
<command>DELETE</command> statement.
When writing a data-modifying statement (<command>INSERT</command>,
--- 214,220 ----
subqueries that can be referenced by name in the primary query.
The subqueries effectively act as temporary tables or views
for the duration of the primary query.
! Each subquery can be a <command>SELECT</command>, <command>TABLE</>, <command>VALUES</command>,
<command>INSERT</command>, <command>UPDATE</command> or
<command>DELETE</command> statement.
When writing a data-modifying statement (<command>INSERT</command>,
*************** SELECT * FROM (SELECT * FROM mytable FOR
*** 1489,1500 ****
<programlisting>
TABLE <replaceable class="parameter">name</replaceable>
</programlisting>
! is completely equivalent to
<programlisting>
SELECT * FROM <replaceable class="parameter">name</replaceable>
</programlisting>
It can be used as a top-level command or as a space-saving syntax
! variant in parts of complex queries.
</para>
</refsect2>
</refsect1>
--- 1489,1505 ----
<programlisting>
TABLE <replaceable class="parameter">name</replaceable>
</programlisting>
! is equivalent to
<programlisting>
SELECT * FROM <replaceable class="parameter">name</replaceable>
</programlisting>
It can be used as a top-level command or as a space-saving syntax
! variant in parts of complex queries. Only the <literal>WITH</>,
! <literal>UNION</>, <literal>INTERSECT</>, <literal>EXCEPT</>,
! <literal>ORDER BY</>, <literal>LIMIT</>, <literal>OFFSET</>,
! <literal>FETCH</> and locking clauses can be used with <command>TABLE</>;
! the <literal>WHERE</> clause and any form of aggregation cannot
! be used.
</para>
</refsect2>
</refsect1>