5

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?

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

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.

  • 3
    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). – Paul Draper Jan 23 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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