November 23rd, 2008 by depesz | Tags: , , , | 8 comments »
Did it help? If yes - maybe you can help me?

On Thursday, 20th of November, Peter Eisentraut committed his own patch, which adds new command to PostgreSQL: TABLE.

While this command doesn't do anything that wasn't available earlier, it's worth mentioning, as it's one of patches that make PostgreSQL compatible with SQL:2008.

Basically new command “TABLE" acts like an alias to ‘SELECT * FROM':


# TABLE pg_attrdef limit 1;
adrelid | adnum | adbin | adsrc
16423 | 3 | {CONST :consttype 16 :consttypmod -1 :constlen 1 :constbyval true :constisnull false :location 104 :constvalue 1 [ 1 0 0 0 ]} | true
(1 row)

You can of course use it also with “WITH" expressions:

# with a as (select 1) TABLE A;
(1 row)

Not sure if anyone will be ever needing it, but hey – it's shorter than ‘select * from' :)

  1. 8 comments

  2. # gregj
    Nov 23, 2008

    personaly – as I convert all ‘select *’ to something meaningfull in all my code, I find this useless, and confusing. I have no idea why they added that to a standard even…
    So, please give me 1-2 reasons why we need it.

  3. # pabloj
    Nov 23, 2008

    Yes, this seems a solution in search of a problem.
    And “select * from …” is always a bad idea, better list needed columns.

  4. # Tom
    Nov 23, 2008

    ‘SELECT * FROM …’ is not _always_ a bad idea. While it should never be used in code that will expect a particular form of result set, it is fine from the command line or when displaying the result set without additional transformations.

    I often use ‘SELECT * FROM relation LIMIT 1’ to quickly get the table or view columns when I’m not sure of the RDBMS engine or of the particular SQL dialect used by that RDBMS or to bypass other similar issues. (NOTE: You can use LIMIT 0 with some possibly most RDBMS, but I never take that chance because why do I want to even have a possiblity I’ll have to redo as LIMIT 1, plus you get an idea of what is held in the fields).

    That said, given that the TABLE command is less likely to be supported so that again, when one is unsure of or unfamiliar with the RDBMS, it seems like a potential waste of time to type ‘TABLE …’ when it will likely return an error and then require typing ‘SELECT * FROM …’ anyway.

  5. Nov 24, 2008

    There is one reason, to be SQL:2008 compliant.

    Don’t use it and it wont hurt you :)


  6. # Vincenzo Romano
    Nov 24, 2008

    Guido is right.
    The only problem I would highlight here is to direct the (human) resources towards things more related to performance or to new features.
    Maybe I’m wrong, but TABLE doesn’t sound as either. So is the SQL:2008 compliancy at the moment.

  7. Nov 24, 2008

    Ah come on, “SELECT * ..”
    is perfect for EXISTS(…) and NOT EXISTS(…) predicates. 😉

  8. # Ivan Voras
    Nov 27, 2008

    Off-topic: Do you take requests? :) Soon after 8.3 was out of the door there was great enthusiasm for replication in 8.4 (IIRC, hot-standby was the proposed flavour) – was it implemented? Can you tell something about it?

  9. Nov 27, 2008

    @Ivan Voras:
    well, as far as I know it wasn’t committed yet – so I can’t write about it.

    As soon as it will be committed – you can expect (with 1-2 days of delay) blogpost about it :)

Leave a comment