Re: Altering a table - positioning new columns

On Mon, 20 Jan 2003 10:15:29 -0600, "Chris Boget" <chris(at)wild(dot)net>
wrote:
>Hmm, I was under the (obvious) (mis)understanding that a tuple was a
>record. Is that not the case? If not, what is it, then?

There may be subtle differences, but for the sake of this conversation
tuple, row, and record mean more or less the same.

>> Each tuple header stores the number of attributes (natts) at the time of
>> its creation. If you query for an attribute with a higher number, you get
>> NULL. I don't think this can be changed easily without breaking lots of
>> things.
>
>How do the new columns fit into the above scheme?

Oh, and attribute = column.

CREATE TABLE t (c1 int, c2 int);
INSERT INTO t VALUES (1, 1);
INSERT INTO t VALUES (2, 2);

-- In reality SELECT natts doesn't work
SELECT natts,* FROM t;
natts | c1 | c2
------+----+----
2 | 1 | 1
2 | 2 | 2

ALTER TABLE t ADD COLUMN c3 int;
-- returns immediately without touching any existing row/tuple/record.
-- You'll love this feature, if you have millions of rows.

INSERT INTO t VALUES (1, 2, 3);
SELECT natts,* FROM t;
natts | c1 | c2 | c3
------+----+----+----
2 | 1 | 1 |
2 | 2 | 2 |
3 | 1 | 2 | 3

On the other hand, ALTER TABLE t ADD COLUMN c3 int AFTER c1;
would require Postgres to convert existing tuples:
natts | c1 | c3 | c2
------+----+----+----
2 | 1 | | 1
2 | 2 | | 2

Servus
Manfred