Re: Altering a table - positioning new columns

On Mon, 20 Jan 2003 12:15:03 -0600, "Chris Boget" <chris@wild.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