Re: [HACKERS] Column ADDing issues

Peter Eisentraut wrote:
>Thirdly, about TODO item
>
>* ALTER TABLE ADD COLUMN to inherited table put column in wrong place
>
>Actually, according to what I would expect out of the blue, it puts it
>into the *right* place. Even good ol' SQL, although they naturally do not
>know about inheritance, seems to agree:
>
>"... the degree of [the table] is increased by 1 and the ordinal position
>of that [new] column is equal to the new degree of [the table] ..."
>(11.11)
>
>What that says to me is that if you add a column to a table (during create
>or alter) then the new column gets placed after all the others. Thus,
>we're in compliance without even knowing it.
>
>Or maybe look at it this way:
>create table test1 (a int4);
>create table test2 (b int4) inherits (test1);
> ^ col #1 ^ col #2
>alter table test1* add column c int4;
> ^ col #3
>
>Everything has its order and it's not like the inheritance as such is
>broken.
>
>Surely, trying to stick the column in between is going to be three times
>as much work as dropping columns would be, whichever way you do it. (And
>making attributes invisible ain't gonna help you. ;)

It is:
create table test1 (a int4);
create table test2 (b int4) inherits (test1);
^ col #2 ^ col #1
alter table test1* add column c int4;
^ col #3 but needs to be #2, since it _is_
#2 of test1

As far as inheritance goes, all the descendants are treated as one table,
including those created on a different branch from test2. All of them
have to return the right columns for a single query; the two options for
dealing with this seem to be logical column numbering, or rewriting the
descendant tables. (But I haven't spent enough time in the code to be
sure of that.)

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"My little children, let us not love in word, neither
in tongue; but in deed and in truth."
I John 3:18