> >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.
Ok. What is the subtle difference in the grand scale of things?
> >> 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.
Ok
> 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.
So I see.
> 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? What if you do a SELECT c3 from
one of the records above that doesn't have a value? If the attributes are set
for each tuple/record at creation, wouldn't you get an error saying that there
is no such attribute/column? 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?
Or will there be an error? If it will update and/or if you can select the value
for that column and get a NULL/non value (and not get an error), isn't that
existing tuple getting touched? And if that's the case, what's the difference
between the illustration above and the illustration below?
> 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
Chris