Re: Altering a table - positioning new columns - Mailing list pgsql-general

From Manfred Koizar
Subject Re: Altering a table - positioning new columns
Date
Msg-id 7tbo2vkbsrgk36m2dkv74a9kkb7tkan3ip@4ax.com
Whole thread Raw
In response to Re: Altering a table - positioning new columns  ("Chris Boget" <chris@wild.net>)
Responses Re: Altering a table - positioning new columns
List pgsql-general
On Mon, 20 Jan 2003 10:15:29 -0600, "Chris Boget" <chris@wild.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

pgsql-general by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: URGENT: dropping constraints and references from a
Next
From: Justin Clift
Date:
Subject: Re: Database Performance problem