Re: Add Column BEFORE/AFTER another column

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Add Column BEFORE/AFTER another column
Дата
Msg-id 29856.1188020561@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Add Column BEFORE/AFTER another column  (Erik Jones <erik@myemma.com>)
Список pgsql-general
Erik Jones <erik@myemma.com> writes:
> ... The usual answer, or argument against, is because the
> standard dictates that the order of attributes in rows returned by
> queries is undefined in the absence a specified (in the query) ordering.

I don't think this is true.  The spec is explicit that *row* order is
indeterminate without an ORDER BY clause, eg SQL92 section 20.2 GR4:

         4) If an <order by clause> is not specified, then the ordering of
            the rows of Q is implementation-dependent.

However, *column* order seems always respected, eg 7.9 rule 3 saith
about SELECT * :

            b) Otherwise, the <select list> "*" is equivalent to a <value
              expression> sequence in which each <value expression> is a
              <column reference> that references a column of T and each
              column of T is referenced exactly once. The columns are ref-
              erenced in the ascending sequence of their ordinal position
              within T.

Another example is that section 11.11 is crystal clear that ALTER TABLE
ADD COLUMN puts the new column at the end:

         4) In all other respects, the specification of a <column
            definition> in an <alter table statement> has the same effect
            as specification of the <column definition> in the <table
            definition> for T would have had. In particular, the degree of T
            is increased by 1 (one) and the ordinal position of that column
            is equal to the new degree of T as specified in the General
            Rules of Subclause 11.4, "<column definition>".


AFAIK the convention that "using SELECT * in code is a bad idea" is
folklore born from years of maintaining real applications; it's surely
good advice, but it's not grounded in any vagueness of the spec.

As far as real PG plans go, I've lost track of the number of requests
we've had for the ability to adjust column order.  To do this in a
reasonably sane fashion, we have to separate logical from physical
column order; and the reason that's not been tackled is fear of bugs
from using the wrong kind of column number in the wrong place.  It'll
probably get done someday, but my advice would be to stay away from
the first major release after it happens ...

            regards, tom lane

В списке pgsql-general по дате отправления:

Предыдущее
От: "Jaime Casanova"
Дата:
Сообщение: Re: Add Column BEFORE/AFTER another column
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: lc_collate issue