Thread: Altering a table - positioning new columns
In MySQL, when adding a new column to a table, you could position it relative to another column already in the DB. IOW, you could do something like this: ALTER TABLE blah ADD COLUMN newcol AFTER anothercol. Is something like this possible in PG? Or are _all_ new columns added to the end of the column list? I've searched through the docs and couldn't find that this was possible and some tests have come up with errors. That said, I'm still new enough to where I can't be sure I'm doing it the right way for PG and am hoping one of you guys can give me a definitive yes or no. thnx, Chris
On Mon, 20 Jan 2003 07:24:57 -0600, "Chris Boget" <chris@wild.net> wrote: >give me a >definitive yes or no. No. Sorry. If column order is important, you have to create a new table and copy over the data, then drop the old table and rename the new one. Of course this has its drawbacks, if there are triggers, foreign keys, ... Servus Manfred
> No. Sorry. *sigh* That kind of bites. I'm curious how difficult this would be to implement... > If column order is important, you have to create a new table and copy > over the data, then drop the old table and rename the new one. Yeah, that's what I've been doing all weekend. > Of course this has its drawbacks, if there are triggers, foreign keys, ... Yeah, these are pretty serious drawbacks. It took me 30+ minutes to do this for just _one_ table. I don't like new fields trailing the column list because I prefer all similar columns to be grouped. That way if you are viewing the data through a UI, it's easier to see/read. But I guess unless I want to spend hours adding a few columns to some tables, I'm just going to have to learn to live with it... :| Chris
"Chris Boget" <chris@wild.net> writes:
> Is something like this possible in PG? Or are _all_ new columns
> added to the end of the column list?
New columns always go at the end of the list.
regards, tom lane
On Mon, 20 Jan 2003, Chris Boget wrote: > > Of course this has its drawbacks, if there are triggers, foreign keys, ... > > Yeah, these are pretty serious drawbacks. It took me 30+ minutes to do > this for just _one_ table. I don't like new fields trailing the column list because > I prefer all similar columns to be grouped. That way if you are viewing the > data through a UI, it's easier to see/read. > But I guess unless I want to spend hours adding a few columns to some > tables, I'm just going to have to learn to live with it... :| > Of course that is purely presentation of the data, nothing to do with integrity of the data, therefore just as the order of rows returned from a query are undefined, unless explicitly ordered, so are the columns. MySQL might be seen as having a good feature if it can manage the column ordering bit but imo it's fud, detracting from the real job of a DB. -- Nigel J. Andrews
> Date: Mon, 20 Jan 2003 07:24:57 -0600 > From: "Chris Boget" <chris@wild.net> > > In MySQL, when adding a new column to a table, you could > position it relative to another column already in the DB. IOW, > you could do something like this: > > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol. > > Is something like this possible in PG? Or are _all_ new columns > added to the end of the column list? > AFAIK there is no concept like "column order of a relation" in the relational model; thus the above MySQL option should not be supported by a relational database. What does the SQL standard say about this? Christoph Dalitz
> > Yeah, these are pretty serious drawbacks. It took me 30+ minutes to do > > this for just _one_ table. I don't like new fields trailing the column list because > > I prefer all similar columns to be grouped. That way if you are viewing the > > data through a UI, it's easier to see/read. > > But I guess unless I want to spend hours adding a few columns to some > > tables, I'm just going to have to learn to live with it... :| > Of course that is purely presentation of the data, nothing to do with integrity > of the data, You are absolutely correct. > therefore just as the order of rows returned from a query are undefined, unless > explicitly ordered, so are the columns. Indeed. "Unless explicitly ordered" is the key phrase. As you can "explicity order" the rows in a query, it would be nice if you could "explicity order" the layout of your table when altered. > MySQL might be seen as having a good feature if it can manage the column > ordering bit but imo it's fud, detracting from the real job of a DB. You are correct. The job of the DB is to keep/hold/serve data. However, you can't overlook the person managing that data. Especially when it comes to large(ish) tables. It make managing the tables/data harder if you have to look all over the place for the fields in a table. Unless you create a table to be 100% first time (and that never happens), you will need to search all over the place. It is nice if you can have all your date fields in one area, flag fields in another as opposed to 3 date fields in the middle of the table, 2 towards the end and another at the very end. If you (general "you") have added a bunch of fields to a table, go into phpPGAdmin, pgAdmin II (or some other UI) and look at it. You'll see what I mean. Again, this is something I could learn to live with. But after using mySQL for 4 years and adding tons of fields to various tables to incorporate new functionality, let me tell you how nice it is to be able to place new fields where you want them in your table. Chris
On Mon, 20 Jan 2003 09:28:50 -0600, "Chris Boget" <chris@wild.net> wrote: >I'm curious how difficult this would be to implement... The current implementation has the benefit that when you add a column existing tuples don't have to be touched. 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. Mid-2002 there has been rudimentary discussion about metadata versioning, but it didn't get beyond its brainstorming phase ... Servus Manfred
> >I'm curious how difficult this would be to implement... > The current implementation has the benefit that when you add a column > existing tuples don't have to be touched. 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? > 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? Chris
After: Create table t ( f1 int, f2 int, f3 int); Alter table t add f4 int; If you do not like the default order, you can: create table tmp as select f1, f4, f2, f3 from t; drop table t; alter table tmp rename to t; JLL Chris Boget wrote: > > > > Yeah, these are pretty serious drawbacks. It took me 30+ minutes to do > > > this for just _one_ table. I don't like new fields trailing the column list because > > > I prefer all similar columns to be grouped. That way if you are viewing the > > > data through a UI, it's easier to see/read. > > > But I guess unless I want to spend hours adding a few columns to some > > > tables, I'm just going to have to learn to live with it... :| > > Of course that is purely presentation of the data, nothing to do with integrity > > of the data, > > You are absolutely correct. > > > therefore just as the order of rows returned from a query are undefined, unless > > explicitly ordered, so are the columns. > > Indeed. "Unless explicitly ordered" is the key phrase. As you can "explicity order" > the rows in a query, it would be nice if you could "explicity order" the layout of your > table when altered. > > > MySQL might be seen as having a good feature if it can manage the column > > ordering bit but imo it's fud, detracting from the real job of a DB. > > You are correct. The job of the DB is to keep/hold/serve data. However, you can't > overlook the person managing that data. Especially when it comes to large(ish) > tables. It make managing the tables/data harder if you have to look all over the > place for the fields in a table. Unless you create a table to be 100% first time (and > that never happens), you will need to search all over the place. It is nice if you > can have all your date fields in one area, flag fields in another as opposed to 3 > date fields in the middle of the table, 2 towards the end and another at the very > end. > If you (general "you") have added a bunch of fields to a table, go into phpPGAdmin, > pgAdmin II (or some other UI) and look at it. You'll see what I mean. > > Again, this is something I could learn to live with. But after using mySQL for 4 > years and adding tons of fields to various tables to incorporate new functionality, > let me tell you how nice it is to be able to place new fields where you want them > in your table. > > Chris > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
I think this is a question of which functionality goes into the actual
database engine, and which functionality is implemented by a front-end tool
like pgAdmin. Obviously, it's possible for the back-end database to handle
this, as MySQL has. This would be through non-standard extensions to SQL.
Another option would be to let the front-end tool let you specify how you
want to see your columns, and remember what order you like to see them in.
This would have the potential advantage of allowing different users to see
the columns in different orders, according to the column order and
groupings that make the most sense to the individual users. Since this is a
feature that's primarily useful for database administration, it might make
more sense to actually put it into the database administration tools.
Wes Sheldahl
"Chris Boget" <chris@wild.net>@postgresql.org on 01/20/2003 10:51:32 AM
Sent by: pgsql-general-owner@postgresql.org
To: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
cc: "Manfred Koizar" <mkoi-pg@aon.at>, "PGSql General"
<pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Altering a table - positioning new columns
(snip)
> therefore just as the order of rows returned from a query are undefined,
unless
> explicitly ordered, so are the columns.
Indeed. "Unless explicitly ordered" is the key phrase. As you can
"explicity order"
the rows in a query, it would be nice if you could "explicity order" the
layout of your
table when altered.
> MySQL might be seen as having a good feature if it can manage the column
> ordering bit but imo it's fud, detracting from the real job of a DB.
You are correct. The job of the DB is to keep/hold/serve data. However,
you can't
overlook the person managing that data. Especially when it comes to
large(ish)
tables. It make managing the tables/data harder if you have to look all
over the
place for the fields in a table. Unless you create a table to be 100%
first time (and
that never happens), you will need to search all over the place. It is
nice if you
can have all your date fields in one area, flag fields in another as
opposed to 3
date fields in the middle of the table, 2 towards the end and another at
the very
end.
If you (general "you") have added a bunch of fields to a table, go into
phpPGAdmin,
pgAdmin II (or some other UI) and look at it. You'll see what I mean.
Again, this is something I could learn to live with. But after using mySQL
for 4
years and adding tons of fields to various tables to incorporate new
functionality,
let me tell you how nice it is to be able to place new fields where you
want them
in your table.
Chris
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Christoph Dalitz <christoph.dalitz@hs-niederrhein.de> writes:
> AFAIK there is no concept like "column order of a relation" in the
> relational model; thus the above MySQL option should not be supported by
> a relational database. What does the SQL standard say about this?
You would think so, but SQL92 does say
b) ... the <select list> "*" is equivalent to a <value
expression> sequence in which each <value expression> is a
<column reference> that references a column of T and each
column of T is referenced exactly once. The columns are ref-
erenced in the ascending sequence of their ordinal position
within T.
(I didn't bother to look it up, but I would imagine there's something
similar defining the behavior of INSERT without a column name list,
which is the only other place that column order matters in SQL, AFAIR.)
ALTER ADD COLUMN says
4) In all other respects, the specification of a <column defi-
nition> in an <alter table statement> has the same effect as
specification of the <column definition> in the <table defi-
nition> for T would have had. In particular, the degree of T
is increased by 1 and the ordinal position of that column is
equal to the new degree of T as specified in the General Rules
of Subclause 11.4, "<column definition>".
and ALTER DROP COLUMN says
6) The degree of T is reduced by 1. The ordinal position of all
columns having an ordinal position greater than the ordinal
position of C is reduced by 1.
So the Postgres behavior is per spec. Whether MySQL's extension is
worth the (nontrivial) trouble it'd be to implement is in the eye of
the beholder.
regards, tom lane
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
Chris, See comments inline below, >>No. Sorry. > > > *sigh* That kind of bites. > I'm curious how difficult this would be to implement... > > >> If column order is important, you have to create a new table and copy >>over the data, then drop the old table and rename the new one. > Sorry to jump in the middle as I may have lost a bit of what you are trying to accomplish, but if you are merely worried about how the columns are grouped visually you could set up views and have them appear in any order you want. You can go as far as making the view updatable as well, but that would really be no different than updating/inserting into the table. > > Yeah, that's what I've been doing all weekend. > > >>Of course this has its drawbacks, if there are triggers, foreign keys, ... > > > Yeah, these are pretty serious drawbacks. It took me 30+ minutes to do > this for just _one_ table. I don't like new fields trailing the column list because > I prefer all similar columns to be grouped. That way if you are viewing the > data through a UI, it's easier to see/read. > But I guess unless I want to spend hours adding a few columns to some > tables, I'm just going to have to learn to live with it... :| > I use to get hung up on the table layout, but now I do my best to group things as well as I can at the time it is created, and if more columns are needed in the future, I either recreate the entire structure or live with the "out of order" columns. In the end when you are inserting with the field/value list it doesn't matter anyways. So, views can be your friend when you require a certain "view" of your data ;) Tim
> >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
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
On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote: > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol. > > Is something like this possible in PG? Or are _all_ new columns > added to the end of the column list? This is exactly the reason why you should almost always acces your data through views. Makes life so much easier. Michael -- Michael Meskes Email: Michael@Fam-Meskes.De ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Manfred Koizar <mkoi-pg@aon.at> writes:
> On the other hand, ALTER TABLE t ADD COLUMN c3 int AFTER c1;
> would require Postgres to convert existing tuples:
It could actually be done without that. If we distinguished logical and
physical column numbers (this'd require a new attlognum column in
pg_attribute), then we could reorganize the apparent column ordering
at will, without touching the contents of any existing tuple. ADD
COLUMN would still assign N+1 as the physical column number of a new
column, but the logical numbering could be relabeled to insert the
new column anywhere.
We discussed this last summer, IIRC, and decided that we didn't want to
get into it because of the probability of introducing ugly new bugs
as a result of using logical attnum in places where physical attnum
is needed, or vice versa. Since in simple test cases the numbers would
be the same, such bugs could escape detection for a long time (just long
enough to fatally trash someone's data :-(). But maybe some day we'll
tackle it.
regards, tom lane
On Mon, Jan 20, 2003 at 08:38:18PM +0100, Michael Meskes wrote:
> On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote:
> > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol.
> >
> > Is something like this possible in PG? Or are _all_ new columns
> > added to the end of the column list?
>
> This is exactly the reason why you should almost always acces your data
> through views. Makes life so much easier.
i've been wondering about this philosophy for a while, now.
maybe we should ALWAYS use views as the API, and never the
underlying tables --
create table _things (
stamp timestamp(0),
fld bigint,
other varchar(10)
);
create function show_fld(bigint)returns text as '
...something like make digits into ##-######-### part number
' language 'plpgsql';
create view things as
select
show_fld( fld ),
show_other( other ),
something_else( yada yada )
from
_things
;
create rule things_add as
on insert to things
do instead (
insert into _things (
stamp,
fld,
other
) values (
current_timestamp,
store_fld( NEW.fld ),
store_fld( NEW.other )
);
);
create rule things_edit as
on update to things
do instead (
...
);
i'm beginning to think that this "always use a view" should be
done for ALL tables, even the lookup/validation tables. is it a
serious performance issue? is there a good reason NOT to do
this?
this would also facilitate changes in the future, i'd think:
relying on views in the application code, we can change the
underlying tables (add some, remove some, alter they way they
interconnect) but the program logic could stay the same.
in some instances. :)
whaddya think?
--
There are 10 kinds of people:
ones that get binary, and ones that don't.
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !
Speaking only for myself, we suffer enough already from ODI dependency hell, because of the largish number of functions, triggers and views that are interdependent in one of our systems. Just having to change the base table schemas slightly leads to a whole round of drops and re-creates of the above objects. On a heavy production system, this tends to be a pain. Perhaps there is a way around this issue that we are not knowing about? In terms of needing the columns to be in a certain order, while it may look nice for documentation purposes, in the main scheme of things, we have gotten used to column displays showing in the order that we made them. If we have the time at any point, we tend to backup the table, adjust the schema by hand, and re-import the data once again. - Ericson Smith http://www.did-it.com http://weightlossfriends.com On Tue, 2003-01-21 at 18:43, will trillich wrote: > On Mon, Jan 20, 2003 at 08:38:18PM +0100, Michael Meskes wrote: > > On Mon, Jan 20, 2003 at 07:24:57AM -0600, Chris Boget wrote: > > > ALTER TABLE blah ADD COLUMN newcol AFTER anothercol. > > > > > > Is something like this possible in PG? Or are _all_ new columns > > > added to the end of the column list? > > > > This is exactly the reason why you should almost always acces your data > > through views. Makes life so much easier. > > i've been wondering about this philosophy for a while, now. > maybe we should ALWAYS use views as the API, and never the > underlying tables -- > > create table _things ( > stamp timestamp(0), > fld bigint, > other varchar(10) > ); > > create function show_fld(bigint)returns text as ' > ...something like make digits into ##-######-### part number > ' language 'plpgsql'; > > create view things as > select > show_fld( fld ), > show_other( other ), > something_else( yada yada ) > from > _things > ; > > create rule things_add as > on insert to things > do instead ( > insert into _things ( > stamp, > fld, > other > ) values ( > current_timestamp, > store_fld( NEW.fld ), > store_fld( NEW.other ) > ); > ); > > create rule things_edit as > on update to things > do instead ( > ... > ); > > i'm beginning to think that this "always use a view" should be > done for ALL tables, even the lookup/validation tables. is it a > serious performance issue? is there a good reason NOT to do > this? > > this would also facilitate changes in the future, i'd think: > relying on views in the application code, we can change the > underlying tables (add some, remove some, alter they way they > interconnect) but the program logic could stay the same. > > in some instances. :) > > whaddya think? > > -- > There are 10 kinds of people: > ones that get binary, and ones that don't. > > will@serensoft.com > http://sourceforge.net/projects/newbiedoc -- we need your brain! > http://www.dontUthink.com/ -- your brain needs us! > > Looking for a firewall? Do you think smoothwall sucks? You're > probably right... Try the folks at http://clarkconnect.org/ ! > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html