6

In PostgreSQL, I've been using syntax like

TABLE example1
UNION ALL
TABLE example2

which I understand to be equivalent to

SELECT * FROM example1
UNION ALL
SELECT * FROM example2

But a colleague asked me questions about this, and I can't find any PostgreSQL docs on this syntax. ("TABLE" isn't a helpful search phrase.) Where can I find documentation about it?

Is it ANSI SQL standard?


EDIT: It currently apparently even confuses StackExchanges's syntax highlighting.

2
  • AFAIR a TABLE table_name is a function which returns SET OF record. Search something near this...
    – Akina
    Jan 23, 2020 at 5:55
  • 2
    @Akina: no, it's not a function
    – user1822
    Jan 23, 2020 at 6:50

2 Answers 2

13

It's documented with the SELECT statement and it's called "TABLE Command" there.

In the SQL standard it's called an "explicit table":

The <explicit table>

   TABLE <table or query name>

is equivalent to the

   ( SELECT * FROM <table or query name> )


This seems to be part of the standard at least since SQL 1992

0

I've been using syntax like "TABLE example1" ... equivalent to ... "SELECT * FROM example1"

... and you really shouldn't be doing either of these.

Always specify the columns that you want to retrieve explicitly.

Databases are inherently shared constructs and you have no control over whether or when someone [else] suddenly dumps two dozen blob fields into your previously tiny table, thereby trashing your application's performance, as it pulls back all these extraneous columns in which your code has precisely zero interest.

2
  • 4
    I usually use this with temporary tables or CTEs, where the table has already been limited to only the columns of interest (and I know the order of those columns). Jan 23, 2020 at 15:35
  • As Paul says, the syntax is useful when using CTEs. Which also shows that besides the possibly misleading name, the TABLE syntax can be used with views and CTEs and not only with base tables. Oct 22, 2020 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.