Re: Altering a table - positioning new columns

On Mon, 20 Jan 2003 12:15:03 -0600, "Chris Boget" <chris(at)wild(dot)net>
wrote:
>> There may be subtle differences, but for the sake of this conversation
>> tuple, row, and record mean more or less the same.
>
>Ok. What is the subtle difference in the grand scale of things?

That's not my realm. I tend to be sloppy regarding choice of words.
Mostly depends on what crosses my mind first when I ploddingly
translate my thoughts into English ...

>> 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
>
>This was an excellent illustration/example, thank you. However, this brings
>up one thing. Isn't PG going to have to touch all the existing records in one
>way or another at some point in time?

No, if touch = change.

> What if you do a SELECT c3 from
>one of the records above that doesn't have a value?

You simply get NULL.

> If the attributes are set

The *number* of attributes ...

>for each tuple/record at creation, wouldn't you get an error saying that there
>is no such attribute/column?

Oh, I think I see what the misunderstanding is. Column *names* are
not stored per tuple. If you SELECT c3 FROM t WHERE c1 = 2;
the column name c3 is looked up in the catalogs, we get the
information that it is the 3rd column, then PG fetches the tuple with
c1 = 2 (it's the tuple with natts = 2 and attribute values 2 and 2).
Now it tries to extract the 3rd attribute from this tuple, sees that
there are less than 3 attributes, and therefore returns NULL.

> When in fact there is? And what happens if
>you try to update that record to set a value for that column? Will it update?

Yes, because in Postgres an UPDATE is basically a DELETE of the old
version and an INSERT with the new values. The new version will have
natts = 3.

Servus
Manfred