Thread: full featured alter table?
Hi, one of the biggest disease of PostGreSQL is, that i can't change the definition of a column. In order to do that, i'd have to drop any keys, drop the column and create a new one with all indexes etc. Are there any plans to overcome that problem? Even simple changes like varchar(20) to varchar(200) are not allowed. I asked this question about 2 years ago, and there were only some guys, that told me that i wouldn't need to change my DB if i'd plan it well. So my DB is planned well, but i have to change it every now and than because i must implement the changes that my client demands me to do, and have some extra work that nobody will pay me for, if there's no way to change a column. This is the only missing feature, that prevent me to use this DBMS - i'd love to, because it's an ORDBMS and that's what i'd have needed sometimes.
The process you describe really is not that difficult. My general method is: Rename existing column Create new column Repopulate from existing column Drop existing column Re-Add constraints/indexes (honestly I don't use constraints that much). Jon On Thu, 12 Jun 2003, Sven Koehler wrote: > Hi, > > one of the biggest disease of PostGreSQL is, that i can't change the > definition of a column. > > In order to do that, i'd have to drop any keys, drop the column and > create a new one with all indexes etc. > > Are there any plans to overcome that problem? > Even simple changes like varchar(20) to varchar(200) are not allowed. > > I asked this question about 2 years ago, and there were only some guys, > that told me that i wouldn't need to change my DB if i'd plan it well. > > So my DB is planned well, but i have to change it every now and than > because i must implement the changes that my client demands me to do, > and have some extra work that nobody will pay me for, if there's no way > to change a column. > > This is the only missing feature, that prevent me to use this DBMS - i'd > love to, because it's an ORDBMS and that's what i'd have needed sometimes. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
> The process you describe really is not that difficult. My general method > is: yes, but it takes time and is error-prone. it a feature that most DBMS have, but postgresql does not, and what you describe is a nice workaround, but not an answer to my question. when will postgresql will finally have a full-featured alter table command?
On Thu, 12 Jun 2003, Sven Koehler wrote: > one of the biggest disease of PostGreSQL is, that i can't change the > definition of a column. > > In order to do that, i'd have to drop any keys, drop the column and > create a new one with all indexes etc. > > Are there any plans to overcome that problem? Pretty much when someone who cares about it enough comes along with a sufficient plan (and preferrably code) to implement it without breaking things would be my guess (especially given that AFAICS it's not part of either SQL92 or SQL99). Note that a sufficient plan would possibly involve a lot of things not directly involved with changing the type such as being able to deal with cached query plans for functions and such. > Even simple changes like varchar(20) to varchar(200) are not allowed. True, but at least these are simple manipulations of the system catalog. > So my DB is planned well, but i have to change it every now and than > because i must implement the changes that my client demands me to do, > and have some extra work that nobody will pay me for, if there's no way > to change a column. Why? If your client asks for a change, they should pay for what's involved. You have to make it known that changes are more costly for this setup up front, but if it has other advantages, they can choose which way to go.
> Pretty much when someone who cares about it enough comes along with > a sufficient plan (and preferrably code) to implement it without breaking > things would be my guess (especially given that AFAICS it's not part of > either SQL92 or SQL99). Note that a sufficient plan would possibly > involve a lot of things not directly involved with changing the type > such as being able to deal with cached query plans for functions and > such. In other word, nobody cares about that at the moment, and so this will not be implemented. something i hate most about opensource movement are these comments like "submit a patch". your staments are not that rough, but i'd need more time to get into the code than to implement the feature. so if this feature was never foreseen, than it might get a heavy task, and it might be even havier in a few years to implement that. even a tool could help to improve that. perhaps pgAdmin could be teached to change a column's type by performing the steps it would need. i'll submit a feature-request to the pgadmin team.
On Thu, 12 Jun 2003, Sven Koehler wrote: > > Pretty much when someone who cares about it enough comes along with > > a sufficient plan (and preferrably code) to implement it without breaking > > things would be my guess (especially given that AFAICS it's not part of > > either SQL92 or SQL99). Note that a sufficient plan would possibly > > involve a lot of things not directly involved with changing the type > > such as being able to deal with cached query plans for functions and > > such. > > In other word, nobody cares about that at the moment, and so this will > not be implemented. Actually, it's more that it's a big job (bigger than actually just changing the column) and it has some side issues about things like storage (can we use 2x space to do it, do we need to do something else, what happens if it fails part way due to something like a failure to convert data), stored plans (do plpgsql functions and foreign keys continue functioning after it, what about set returning functions that return that type), and probably other things that I can't think of. With limited resources, you have to make choices about what you work on. I'm sure Tom (for example) could have done it, but he possibly wouldn't have been able to do some or all of the error code stuff, protocol changes, expression indexes, various optimizer enhancements, bug fixes, etc. > something i hate most about opensource movement are these comments like > "submit a patch". your staments are not that rough, but i'd need more > time to get into the code than to implement the feature. > so if this feature was never foreseen, than it might get a heavy task, > and it might be even havier in a few years to implement that. It's actually probably going to move towards being easier over time hopefully, since some of the side issues are things that are probably going to be addressed anyway.
I would agree with you Sven, I really like that feature to be added too, because that's one of the important reasons I still prefer mysql whenever I don't have to use other features that postgres has. - reynard Sven Koehler wrote: > In other word, nobody cares about that at the moment, and so this will > not be implemented. > > something i hate most about opensource movement are these comments > like "submit a patch". your staments are not that rough, but i'd need > more time to get into the code than to implement the feature. > so if this feature was never foreseen, than it might get a heavy task, > and it might be even havier in a few years to implement that. > > even a tool could help to improve that. perhaps pgAdmin could be > teached to change a column's type by performing the steps it would need. > > i'll submit a feature-request to the pgadmin team.
On Thu, 12 Jun 2003, Sven Koehler wrote: > Hi, > > one of the biggest disease of PostGreSQL is, that i can't change the > definition of a column. > > In order to do that, i'd have to drop any keys, drop the column and > create a new one with all indexes etc. > > Are there any plans to overcome that problem? > Even simple changes like varchar(20) to varchar(200) are not allowed. > > I asked this question about 2 years ago, and there were only some guys, > that told me that i wouldn't need to change my DB if i'd plan it well. > > So my DB is planned well, but i have to change it every now and than > because i must implement the changes that my client demands me to do, > and have some extra work that nobody will pay me for, if there's no way > to change a column. > > This is the only missing feature, that prevent me to use this DBMS - i'd > love to, because it's an ORDBMS and that's what i'd have needed sometimes. OK, so how do we handle things like converting a varchar to a timestamp with time zone? What if one of the rows has invalid date syntax? Do we convert the column anyway, or throw the whole change out with an error? What about converting int8 to int4 where we have integers that are LARGER than what an int4 can hold. Do we just set the int4 to max/min when the int8 is out of range? How about converting varchar(64) to varchar(32). Do you want it to error out if there are any fields over 32 chars, or just chop them off? MySQL has a "feature" like the one you want. It's not well thought out, and easily causes as much misery and pain as it prevents. If you convert a column to date type it silently converts any field it can't suss out to the very wonderful value of 0000-00-00. Since their DDL is not transactable, you've just lost all your dates. Hope you had a backup laying around. Converting column types is like walking around with a gun pointed at your foot. If the possible error cases aren't well thought out, then bam! the gun goes off and your data is so much mush. Other vendors may not care as much about data integrity as the postgresql team, and if you prefer their product feel free to use it. The Postgresql team has a tendency of not incorporating such changes until they can work for virtually all cases without error. Here's how I do it: # pg_dump -a -t tabletochange >tabledata.sql # pg_dump -s -t tabletochange >tableschema.sql vi tableschema.sql (make changes) psql testdatabase <tableschema.sql psql testdatabase <tabledata.sql psql testdatabase look and see if your data is still there. Changing column types is playing fast and loose with your data and is not recommended. It's also, as near as I can tell, not in the SQL3 spec. It may bug you that open source projects say "submit a patch and we'll see" but that's way better than "oh yeah, we have that feature. what? Your data got mucked up? Wow, that's never happened before."
>OK, so how do we handle things like converting a varchar to a timestamp >with time zone? What if one of the rows has invalid date syntax? Do we >convert the column anyway, or throw the whole change out with an error? > being a developer instead of DBA makes me think little about the danger of losing data when you change column type. But, I think you're right, very BAD things could happen with your data if the database silently convert / truncate your data when you change the column type. As far as I remember when working with Oracle, it allows you to change the data as long as you don't lose or corrupt your data. So, for example, changing varchar(20) to varchar(40) should be ok, but the reverse might not be ok. It'd be nice if it allows you to change the type from varchar(40) to varchar(20) if you don't have any data that is larger than varchar(20). I don't know how much complexity that will add, though. well that's just some idea from me. - reynard
On Fri, Jun 13, 2003 at 05:05:32PM +0000, Reynard Hilman wrote: > As far as I remember when working with Oracle, it allows you to change > the data as long as you don't lose or corrupt your data. So, for > example, changing varchar(20) to varchar(40) should be ok, but the > reverse might not be ok. It'd be nice if it allows you to change the > type from varchar(40) to varchar(20) if you don't have any data that is > larger than varchar(20). I don't know how much complexity that will add, > though. well that's just some idea from me. It's not _that_ hard IMHO. It's just that no one has ever bothered to code it. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El dia que dejes de cambiar dejaras de vivir"
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> OK, so how do we handle things like converting a varchar to a timestamp
> with time zone? What if one of the rows has invalid date syntax? Do we
> convert the column anyway, or throw the whole change out with an error?
I think this particular point is a red herring. We have data conversion
functions. My thought would be to apply the same cast function we
would if you were doing an implicit-cast assignment. That is, the
behavior would be exactly like
ALTER TABLE tab ADD COLUMN newcol newtype;
UPDATE tab SET newcol = oldcol;
If there is no cast function, or the cast function burps at any row,
then the command fails and rolls back ... then it's up to you to fix
the data and try again, or use a manual process with some more-complex
conversion function.
However, there are plenty of much-more-subtle semantic issues to worry
about. Here are a couple:
* Indexes. How do you translate an index definition involving one
datatype into an index involving another? There may be no index opclass
at all for the given index type and the new datatype, or there might be
multiple opclasses. Consider for example the recent discussions about
providing reverse-sort opclasses standardly. I'm not sure how ALTER
TABLE could pick the right opclass if there's more than one choice.
* Constraints. If the old column has, say, CHECK (foo(x) > 0), how do
we translate this to a new datatype? Should we assume that if we can
find a function named foo() on the new datatype, it's the right thing
to use? Seems like a mighty dangerous assumption to me.
Functional indexes present *both* of these sets of problems, and I think
there are probably other issues lurking in the advanced features.
Now a human working through the conversion process by hand probably
wouldn't have a lot of problems deciding what to do, but I'm unconvinced
that an automatic ALTER command would get these things right.
What would make more sense to me than a tightly-wrapped-up ALTER command
at the SQL level is some sort of interactive conversion wizard, perhaps
as part of phppgadmin or Red Hat's graphical tool suite. It could walk
you through all these considerations, get your okay on each nontrivial
semantic change, and then apply all the operations within a single
transaction. AFAIK we have all the ALTER functionality needed to
support such a tool.
regards, tom lane
On Fri, Jun 13, 2003 at 05:05:32PM +0000, Reynard Hilman wrote: <snip> > It'd be nice if it allows you to change the type from varchar(40) > to varchar(20) if you don't have any data that is larger than varchar(20). Yeah, and then an application comes in and wants to write more than 20 chars ... well, i dont like such size limitations at all - i'm using "text" instead. what about efficiency ? does it bring _anything_ to limit the size of varchar fields ? > I don't know how much complexity that will add, though. well that's > just some idea from me. hmm, i dont think, its really needed. if you really want to do that, you should also think _very carefully_ 'bout what you're doing. and so you can type the 3 more statements to create a new table, copy the data, drop the old table and rename the new one. cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
On Fri, Jun 13, 2003 at 06:49:01PM -0400, Tom Lane wrote: > What would make more sense to me than a tightly-wrapped-up ALTER command > at the SQL level is some sort of interactive conversion wizard, perhaps > as part of phppgadmin or Red Hat's graphical tool suite. It could walk > you through all these considerations, get your okay on each nontrivial > semantic change, and then apply all the operations within a single > transaction. AFAIK we have all the ALTER functionality needed to > support such a tool. The one thing we don't have that I think would be useful is a way to re-order the columns in a table. Maybe it's just me, but I tend to want column to appear in a specific order, and the only way you can accomplish this today is by re-creating the entire table. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
> The one thing we don't have that I think would be useful is a way to > re-order the columns in a table. Maybe it's just me, but I tend to want > column to appear in a specific order, and the only way you can > accomplish this today is by re-creating the entire table. i agree. i'd love to "reorder" columns.
Le Samedi 14 Juin 2003 15:34, Sven Köhler a écrit : > > The one thing we don't have that I think would be useful is a way to > > re-order the columns in a table. Maybe it's just me, but I tend to want > > column to appear in a specific order, and the only way you can > > accomplish this today is by re-creating the entire table. > > i agree. i'd love to "reorder" columns. > Same for me. It would be great to be able to reorganize some tables... -- Guillaume <!-- http://absfr.tuxfamily.org/ -->.
On Sat, Jun 14, 2003 at 10:31:02 -0500, "Jim C. Nasby" <jim@nasby.net> wrote: > > The one thing we don't have that I think would be useful is a way to > re-order the columns in a table. Maybe it's just me, but I tend to want > column to appear in a specific order, and the only way you can > accomplish this today is by re-creating the entire table. You can specify the order that columns are output now. The real underlying order should really be up to postgres to decide based on what is most efficient. If you were thinking about the order you get when you use * as a shorthand for all of the columns, perhaps there could be some way to change that without actually changing the order of the columns in the table. I doubt it would be a good idea to do that though. It is already a bad idea to use * in applications in case something does change the order or number of columns. * is mostly going to be used in adhoc queries where you care about saving some typing. It may not be worth adding a feature like that for the limited amount of use it would get.
Hello everybody, > The one thing we don't have that I think would be useful is a > way to re-order the columns in a table. Maybe it's just me, > but I tend to want column to appear in a specific order, and > the only way you can accomplish this today is by re-creating > the entire table. I agree with Jim, this would be a 'cool but not mandatory' feature ! :-) This feature is not mandatory since I avoid the use SELECT * FROM... (and I forbid the use of SELECT * to my subordinates). But, it would be very nice to have the possibility order the different columns of a table in PostgreSQL. Why ? For example, I use a reverse-engineering tool that generate graphical database map but that tool don't give me the possibility to change the display order... So, this feature would be very nice ! :-) --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
>>>>> "Jim" == Jim C Nasby <jim@nasby.net> writes: Jim> The one thing we don't have that I think would be useful is a way to Jim> re-order the columns in a table. Maybe it's just me, but I tend to want Jim> column to appear in a specific order, and the only way you can Jim> accomplish this today is by re-creating the entire table. Column order shouldn't matter. It matters only when you say "SELECT *", and the best literature out there warns against the foibles of such. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
> You can specify the order that columns are output now. The real > underlying > order should really be up to postgres to decide based on what is most > efficient. how can i specify it? i think we all ment cosmetical changes, not the "real" underlying order which should be up to postgresql of course! > If you were thinking about the order you get when you use * as a > shorthand > for all of the columns, perhaps there could be some way to change that > without actually changing the order of the columns in the table. "select *" should refelect the cosmetical order of the columns. "select *" could be tranformed into something like "select col1, col2, ..." according to the cosmetical order that's defined. if that's not the case at the moment, i don't care, because i don't use "select *" by assuming any column-order for the reasons you already mentioned.
>>> You can specify the order that columns are output now. The real >>> underlying >>> order should really be up to postgres to decide based on what is >>> most >>> efficient. >> >> how can i specify it? >> i think we all ment cosmetical changes, not the "real" underlying >> order which should be up to postgresql of course! let's express it another way: we want to define the order of the columns when they are shown in tools like phpPgAdmin or PgAdminII. there should be some statement which is used by the tools to query the list of columns in the table - the resultset contains the columns in a defined order which is - in most cases - the order in which the columns were created. we'd like to be abled influence the order of the columns within that resultset. >>> If you were thinking about the order you get when you use * as a >>> shorthand >>> for all of the columns, perhaps there could be some way to change >>> that >>> without actually changing the order of the columns in the table. >> >> "select *" should refelect the cosmetical order of the columns. >> "select *" could be tranformed into something like "select col1, >> col2, ..." according to the cosmetical order that's defined. >> if that's not the case at the moment, i don't care, because i don't >> use "select *" by assuming any column-order for the reasons you >> already mentioned. > > It sounds what you are looking for is soem way to set the order so > that you can save some typing when doing adhoc queries? no - i don't do any "select *" - it's just a cosmetical thing, because after some time - especially when you have to perform the discussed steps to change a column definition - your columns will mix-up. Usually the order of the columns in a table follows some intension of the user who created it.
> Jim> The one thing we don't have that I think would be useful is a way to > Jim> re-order the columns in a table. Maybe it's just me, but I tend to want > Jim> column to appear in a specific order, and the only way you can > Jim> accomplish this today is by re-creating the entire table. > > Column order shouldn't matter. It matters only when you say "SELECT *", > and the best literature out there warns against the foibles of such. I think we don't want to reorder our column to make "select *" have a order that fits our needs. as i said in my other posts, we only want it for our selves to have a defined order, that doesn't look that much chaotic.
On Sat, Jun 14, 2003 at 19:35:15 +0200, Sven Köhler <skoehler@upb.de> wrote: > > let's express it another way: we want to define the order of the columns > when they are shown in tools like phpPgAdmin or PgAdminII. That is something that those tools should do then. It isn't really a postgres issue as it already provides ways for the tools to get information about columns of a table.
>>let's express it another way: we want to define the order of the columns >>when they are shown in tools like phpPgAdmin or PgAdminII. > > That is something that those tools should do then. It isn't really a > postgres issue as it already provides ways for the tools to get > information about columns of a table. The problem is, that the information should be stored somewhere in the database. If each tool does maintain its own column-order storage, that we'll have a chaos.
Bruno Wolff III <bruno@wolff.to> writes:
> Sven K�hler <skoehler@upb.de> wrote:
>> let's express it another way: we want to define the order of the columns
>> when they are shown in tools like phpPgAdmin or PgAdminII.
> That is something that those tools should do then. It isn't really a
> postgres issue as it already provides ways for the tools to get
> information about columns of a table.
Perhaps, but pg_attribute is the natural place to keep the column
ordering info.
ISTM we talked months ago about adding an "attlognum" column to
pg_attribute, which would be separate from "attnum" (the physical column
position) and would be used by the backend only to determine the order
of SELECT * expansion. Admin tools could use this to determine column
display order too. It didn't get done, but it still seems like a
reasonable idea to me.
regards, tom lane
> Perhaps, but pg_attribute is the natural place to keep the column > ordering info. i don't know what pg_attribute is, but sound to me like it is a good sollution. > ISTM we talked months ago about adding an "attlognum" column to > pg_attribute, which would be separate from "attnum" (the physical column > position) and would be used by the backend only to determine the order > of SELECT * expansion. Admin tools could use this to determine column > display order too. It didn't get done, but it still seems like a > reasonable idea to me. it wouldn't matter much to me, if "select *" wouldn't be expanded in the correct order, but it might worry other users. the first thing to achieve is to define and offer a place where tools can store the ordering information. the second would be to make "select *" to use this information, although this information should be well cached than if "select *" is expected to be used often.
Hi, Reynard Hilman wrote: > >> OK, so how do we handle things like converting a varchar to a >> timestamp with time zone? What if one of the rows has invalid date >> syntax? Do we convert the column anyway, or throw the whole change >> out with an error? >> > being a developer instead of DBA makes me think little about the danger > of losing data when you change column type. But, I think you're right, > very BAD things could happen with your data if the database silently > convert / truncate your data when you change the column type. > As far as I remember when working with Oracle, it allows you to change > the data as long as you don't lose or corrupt your data. So, for > example, changing varchar(20) to varchar(40) should be ok, but the > reverse might not be ok. It'd be nice if it allows you to change the > type from varchar(40) to varchar(20) if you don't have any data that is > larger than varchar(20). I don't know how much complexity that will add, > though. well that's just some idea from me. This of course would be convenient, but otoh it would add a great value of complexity to the backend for a rarely used feature. At least it should be rarely used :) For the rare cases where someone has to change column type, maybe its more easy if (s)he does this by hand - thus not complaining to the overloaded core-developers about data loss or unexpected results. The only thing which is a bit complicated is the disabling/enabling trigger sequence which can be borrowed from pg_dump output. The remaining part is possible to put all in one transaction; or at least the delete rows/copy back part: create table ... as select .... from original ...; delete from original; alter table original create column (with new type) alter table remove old column insert into original select * from temptable Regards Tino
On Sat, Jun 14, 2003 at 07:35:15PM +0200, Sven K?hler wrote: <snip> > let's express it another way: we want to define the order of the columns > when they are shown in tools like phpPgAdmin or PgAdminII. > > there should be some statement which is used by the tools to query the > list of columns in the table - the resultset contains the columns in a > defined order which is - in most cases - the order in which the columns > were created. we'd like to be abled influence the order of the columns > within that resultset. this does _NOT_ belong into the postmaster. you're free to define your own tables for storing this (which are not shown to the user in your frontend). pgaccess goes this way. cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
>>there should be some statement which is used by the tools to query the >>list of columns in the table - the resultset contains the columns in a >>defined order which is - in most cases - the order in which the columns >>were created. we'd like to be abled influence the order of the columns >>within that resultset. > > this does _NOT_ belong into the postmaster. you're free to define > your own tables for storing this (which are not shown to the user > in your frontend). pgaccess goes this way. what are you talking about? it is not _my_ frontend! we're talking "frontends" about pgAdmin, phpPgAdmin etc. we are talking about the order of the columns that postgresql shows to it's clients - and yes: this data belongs somewhere into the sys-tables of postgre.
>>It'd be nice if it allows you to change the type from varchar(40) >>to varchar(20) if you don't have any data that is larger than varchar(20). > > Yeah, and then an application comes in and wants to write more than > 20 chars ... why should i define a column as varchar(20) and should than want to write more data than that? i'd only define it as varchar(20) if i'm sure that 20 chars are enough. > well, i dont like such size limitations at all - i'm using "text" instead. > what about efficiency ? does it bring _anything_ to limit the size > of varchar fields ? well - than use text instead of varchar.
On Sat, Jun 14, 2003 at 10:18:30PM +0200, Sven K?hler wrote: <snip> > what are you talking about? it is not _my_ frontend! > we're talking "frontends" about pgAdmin, phpPgAdmin etc. yes. why couldnt they simply use an extra table for this - just like pgaccess does for storing its config ? user-side column odering is frontend stuff. it dont like the idea that SELECT * doesnt use the physical order (from pg_attribute - which you also can see w/ \d in the pgsql frontend) cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
On Sat, Jun 14, 2003 at 10:21:16PM +0200, Sven K?hler wrote: <snip> > >Yeah, and then an application comes in and wants to write more than > >20 chars ... > > why should i define a column as varchar(20) and should than want to > write more data than that? > i'd only define it as varchar(20) if i'm sure that 20 chars are enough. Perhaps there's still some code which still expects longer fields ? especially in larger applications w/ many developers/admis this can be really dangerous. well, if you really want to do this, an perl script which creates a new table and copies the data could also suit your needs. we shouldnt make the postmaster codebase too big. each single line can contain many errors. cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
At 10:32 PM 06/14/2003 +0200, weigelt@metux.de wrote: >On Sat, Jun 14, 2003 at 10:18:30PM +0200, Sven K?hler wrote: > ><snip> >> what are you talking about? it is not _my_ frontend! >> we're talking "frontends" about pgAdmin, phpPgAdmin etc. >yes. why couldnt they simply use an extra table for this - just >like pgaccess does for storing its config ? >user-side column odering is frontend stuff. >it dont like the idea that SELECT * doesnt use the physical order >(from pg_attribute - which you also can see w/ \d in the pgsql frontend) I tend to agree that column ordering does not belong in that database. Simply because I find it hard to believe that yuo would only and always want "SELECT *" to come across to the client in a specific order. Context indicates what the column order needs to be. One other wrinkle is what to do wth "SELECT * from tablea, tableb" Do you use all of tablea and then all of tableb? Or interleave with (first from tablea, first from tableb, second from tablea, second from tableb...) Besides I usually load a record in a dictionary structure in my client language (python or smalltalk) which means a) the order is based on the hash ordering of the dictionary structure anyway and b) I'm doing things by name, not position. Take care, Jay
>>why should i define a column as varchar(20) and should than want to >>write more data than that? >>i'd only define it as varchar(20) if i'm sure that 20 chars are enough. > > Perhaps there's still some code which still expects longer fields ? > especially in larger applications w/ many developers/admis this can > be really dangerous. well - you assume that i don't know the needs of my app. i know what i do, so i know why i change a column to varchar(20).
>>what are you talking about? it is not _my_ frontend! >>we're talking about "frontends" like pgAdmin, phpPgAdmin etc. > > yes. why couldnt they simply use an extra table for this - just > like pgaccess does for storing its config ? > user-side column odering is frontend stuff. > it dont like the idea that SELECT * doesnt use the physical order > (from pg_attribute - which you also can see w/ \d in the pgsql frontend) so why don't you like it to use the user-defined order? do _you_ use "select *" ? BTW: if you change the user-defined order, it will match the physical order, so you it's something you don't have to care about. If the postgresql-team decides, that the column-order is not to be stored in the sys-tables, that it woul be the last step to store it in some other tables, but that is the worst step, as each tool will surely have it own tables etc. if it ones to support column ordering. if you think that column-ordering is just luxury, than i might remind you, table-names and column-names are just luxury too ;-)
> I tend to agree that column ordering does not belong in that database. > Simply because I find it hard to believe that yuo would only and always > want "SELECT *" to come across to the client in a specific order. Context > indicates what the column order needs to be. how many times must i pretend, that "SELECT *" is the last thing i'm worrying about, huh?
On Sun, 15 Jun 2003, Sven Köhler wrote: > >>what are you talking about? it is not _my_ frontend! > >>we're talking about "frontends" like pgAdmin, phpPgAdmin etc. > > > > yes. why couldnt they simply use an extra table for this - just > > like pgaccess does for storing its config ? > > user-side column odering is frontend stuff. > > it dont like the idea that SELECT * doesnt use the physical order > > (from pg_attribute - which you also can see w/ \d in the pgsql frontend) > > so why don't you like it to use the user-defined order? do _you_ use > "select *" ? > BTW: if you change the user-defined order, it will match the physical > order, so you it's something you don't have to care about. > > If the postgresql-team decides, that the column-order is not to be > stored in the sys-tables, that it woul be the last step to store it in > some other tables, but that is the worst step, as each tool will surely > have it own tables etc. if it ones to support column ordering. > > if you think that column-ordering is just luxury, than i might remind > you, table-names and column-names are just luxury too ;-) Well, on the basis that column ordering is presentation issue it does belong in the frontend. However, as Tom pointed out this discussion was had a few months. I can't remember the outcome but apparently a suggestion was to have a column in pg_attribute giving the presentational order. The archives throw up: http://archives.postgresql.org/pgsql-hackers/2002-09/msg00966.php however I don't think Christopher has completely covered the issues. The idea for implementing this is so tools, such as pgaccess, can list a table's columns in some arbitrary order, presumably by someone displaying the table and drag and dropping a column from one position to another. What about the support for that? To change pg_attribute the tool has to be connected as a superuser or there has to be some command, probably part of ALTER TABLE? And then there is the issue of priviledge to use that, you don't want the column order as shown to you changed just because some other person wanted to see them in a different order. Imagine if both were working at the same time and continually trying to get the display order they want. -- Nigel J. Andrews
At 12:33 15.06.2003, Nigel J. Andrews said:
--------------------[snip]--------------------
>Well, on the basis that column ordering is presentation issue it does
>belong in
>the frontend. However, as Tom pointed out this discussion was had a few
>months. I can't remember the outcome but apparently a suggestion was to have a
>column in pg_attribute giving the presentational order.
--------------------[snip]--------------------
Holdit.
This whole thread eveolves to something asking a car designer to design a
car in a way a particular color would come best...
I believe column ordering should be decided by the database itself, to
maximize output/throughput or otherwise beneficially influent performance
and/or disk usage. If you want a specific column sequence, put it into the
SELECT statement. '*' just means "gimme all of them", not in a particular
order.
If some frontende decides to display the columns in a particular order, ok,
but it should store this order somewhere else, either in its own private
tables, a configuration or ini file, or elsewhere. It simply doesn't belong
to the database layout, or pg_attribute.
Just my 2c,
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger wrote: > At 12:33 15.06.2003, Nigel J. Andrews said: > --------------------[snip]-------------------- > >>Well, on the basis that column ordering is presentation issue it does >>belong in >>the frontend. However, as Tom pointed out this discussion was had a few >>months. I can't remember the outcome but apparently a suggestion was to have a >>column in pg_attribute giving the presentational order. > > --------------------[snip]-------------------- > > Holdit. > > This whole thread eveolves to something asking a car designer to design a > car in a way a particular color would come best... > > I believe column ordering should be decided by the database itself, to > maximize output/throughput or otherwise beneficially influent performance > and/or disk usage. If you want a specific column sequence, put it into the > SELECT statement. '*' just means "gimme all of them", not in a particular > order. The proposal does beg the question: Why would a default tuple-attribute order be stored in the database but not relation-tuple order? Mike Mascari mascarm@mascari.com
Ernest E Vogelsinger <ernest@vogelsinger.at> writes:
> If some frontende decides to display the columns in a particular order, ok,
> but it should store this order somewhere else, either in its own private
> tables, a configuration or ini file, or elsewhere. It simply doesn't belong
> to the database layout, or pg_attribute.
The trouble with that rationale is that if you are using several
different tools, you'd probably like them to agree on what the column
ordering is. If the tools all have to invent their own private storage
methods for ordering info, this will never happen.
Also, if history is any guide, people will not like admin tools that
clutter the database with add-on tables in which to store info like
this. pgAdmin (or was it pgaccess?) used to do that some versions back,
and it was a big annoyance. Perhaps the availability of schemas in
current releases would mitigate that problem, but I'd still prefer to
use a tool that doesn't need such things.
Finally, no tool can affect the behavior of "SELECT *" or INSERT-without-
a-column-list, if there's no support for it in the database. Whatever
your opinions on the safety of using these constructs in application
code, people do use 'em a lot in hand-typed SQL. If these constructs
do not follow the column order that a user is used to seeing in his
admin tool, you are opening yourself up to problems.
regards, tom lane
Mike Mascari <mascarm@mascari.com> writes:
> The proposal does beg the question:
> Why would a default tuple-attribute order be stored in the database
> but not relation-tuple order?
A good argument in terms of pure relational-calculus theory ... but
we're dealing with SQL here, not relational calculus. The SQL spec
explicitly states that column ordering is significant while row ordering
is not.
regards, tom lane
Hi, Tom Lane wrote: ... > The trouble with that rationale is that if you are using several > different tools, you'd probably like them to agree on what the column > ordering is. If the tools all have to invent their own private storage > methods for ordering info, this will never happen. ... > Finally, no tool can affect the behavior of "SELECT *" or INSERT-without- > a-column-list, if there's no support for it in the database. Whatever > your opinions on the safety of using these constructs in application > code, people do use 'em a lot in hand-typed SQL. If these constructs > do not follow the column order that a user is used to seeing in his > admin tool, you are opening yourself up to problems. ... why not just order the output columns in alphabetical order? This is quite easy to implement and requires no additional storage. The output would be the same all the time and with all tools. People again should be warned to use select * in applications, but this is another thing... Just my 0.02c Regards Tino
Hello, > why not just order the output columns in alphabetical order? > This is quite easy to implement and requires no additional > storage. The output would be the same all the time and with all tools. No, what we are talking about is to have the possibility to choose the output order of the columns of a table (by adding a field in the pg_attribute table). I don't like GUI applications that creates their own tables in my schema to store ordering or displaying informations. All the GUI that can manages PostgreSQL database could use the pg_attribute table to store the output order of the columns of the table. I think that displays the output columns in alphabetical order will not bring anything interesting... But if the pg_attributes table can be enhanced to store order output, you could have the possibility to sort yourself the ouput order. (Logical order, alphabetical order, or another order...). The pg_attributes table would store (as I imagine) the ordering number of each column. > People again should be warned to use select * > in applications, but this is another thing... I agree with you, SELECT * must be avoided as much as possible. Regards, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
Nigel J. Andrews wrote : ... > I believe column ordering should be decided by the database > itself, to maximize output/throughput or otherwise > beneficially influent performance and/or disk usage. If you > want a specific column sequence, put it into the SELECT > statement. '*' just means "gimme all of them", not in a > particular order. We are not talking about choose the internal column ordering, but the output column ordering. This is the columns presentation order that we would like to have the possibility to change. I think that having the possibility to change the columns presentation order would be a really nice feature because on some large applications you don't have the time to build a temporary table (with a CREATE TABLE temp_table AS SELECT field1,field3,field2 FROM table) and to re-add all the constraints, triggers and others things... So, having the possibility to change that order using a ALTER TABLE would be really time-saving ! > If some frontende decides to display the columns in a > particular order, ok, but it should store this order > somewhere else, either in its own private tables, a > configuration or ini file, or elsewhere. It simply doesn't > belong to the database layout, or pg_attribute. I disagree, I don't like when frontends and other GUI are creating new tables in the database. Plus, if I use a frontend (pgAdmin, for example) to set the columns order, I want to get that presentation order when I do a SELECT * using the PostgreSQL API or using the psql frontend... So, storing the columns order in the pg_attributes table is, I think, a good solution. :-) Regards, -------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
Tino Wildenhain <tino@wildenhain.de> writes:
> why not just order the output columns in alphabetical order?
Because it would violate the SQL spec. While the spec does not propose
any ALTER TABLE command that would let one rearrange the column order,
it is perfectly clear about the semantics of "SELECT *" and "INSERT"
when one has only done CREATE TABLE and ALTER TABLE ADD COLUMN. We do
not get to invent our own behavior there.
regards, tom lane
At 23:30 15.06.2003, Bruno BAGUETTE said:
--------------------[snip]--------------------
>> If some frontende decides to display the columns in a
>> particular order, ok, but it should store this order
>> somewhere else, either in its own private tables, a
>> configuration or ini file, or elsewhere. It simply doesn't
>> belong to the database layout, or pg_attribute.
>
>I disagree, I don't like when frontends and other GUI are creating new
>tables in the database. Plus, if I use a frontend (pgAdmin, for example)
>to set the columns order, I want to get that presentation order when I
>do a SELECT * using the PostgreSQL API or using the psql frontend...
>
>So, storing the columns order in the pg_attributes table is, I think, a
>good solution. :-)
--------------------[snip]--------------------
This may come in handy of you're the only one using the database.
What if:
a) multiple users/developers are "maintaining" their own "private" column
ordering?
b) what happens to those (poorly written but existing) applications that
use columnless INSERTs? Will they break or what?
I don't think changing the column order in pg_attributes would be a good
thing - too much to break loose. Maybe there could be something non-SQL
conforming
CREATE COLUMNORDER "order_name" ON TABLE "table_name" ("column", ...)
and you could use this with a SELECT extension:
SELECT * FROM "table_name" COLUMNORDER "order_name"
but that can be easily done using views:
CREATE VIEW "viewname" AS SELECT (col3, col5, col2) FROM "table_name"
Well, views are read-only unless you're using an appropriate rule.
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
On Sat, Jun 14, 2003 at 10:59:08AM -0500, Bruno Wolff III wrote: > On Sat, Jun 14, 2003 at 10:31:02 -0500, > "Jim C. Nasby" <jim@nasby.net> wrote: > > > > The one thing we don't have that I think would be useful is a way to > > re-order the columns in a table. Maybe it's just me, but I tend to want > > column to appear in a specific order, and the only way you can > > accomplish this today is by re-creating the entire table. > > You can specify the order that columns are output now. The real underlying > order should really be up to postgres to decide based on what is most > efficient. Actually, I was talking about the underlying order, because (at least the last time I asked about this), pgsql doesn't do any intelligent ordering under the covers. Because of that, I'd like to be able to order based on the optimal ordering I was given (fixed width before variable, then not null before null). Of course it would certainly be best if pgsql would automagically optimize internal storage order for me. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Sat, Jun 14, 2003 at 06:55:36PM +0200, Bruno BAGUETTE wrote: > Hello everybody, > > > The one thing we don't have that I think would be useful is a > > way to re-order the columns in a table. Maybe it's just me, > > but I tend to want column to appear in a specific order, and > > the only way you can accomplish this today is by re-creating > > the entire table. > > I agree with Jim, this would be a 'cool but not mandatory' feature ! :-) > > This feature is not mandatory since I avoid the use SELECT * FROM... > (and I forbid the use of SELECT * to my subordinates). 99.999% of the time, if you put SELECT * into code, you should be strung up by your own entrails. But do you mean to tell me that when you're testing stuff on the command line you never, ever use SELECT *? BTW, de-coupling column definition and presentation order from internal column order in the tuples is mandatory before PGSQL could store things internally in the most optimal order. I find it very interesting that many people assumed that PGSQL ordered columns however it wanted in the tuples; I don't know of any major database that does this, even though it probably makes a lot of sense (of course some DBA's would be very unhappy about the loss of control, so you'd probably want an over-ride). -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Mon, 16 Jun 2003, Jim C. Nasby wrote: > On Sat, Jun 14, 2003 at 10:59:08AM -0500, Bruno Wolff III wrote: > > On Sat, Jun 14, 2003 at 10:31:02 -0500, > > "Jim C. Nasby" <jim@nasby.net> wrote: > > > > > > The one thing we don't have that I think would be useful is a way to > > > re-order the columns in a table. Maybe it's just me, but I tend to want > > > column to appear in a specific order, and the only way you can > > > accomplish this today is by re-creating the entire table. > > > > You can specify the order that columns are output now. The real underlying > > order should really be up to postgres to decide based on what is most > > efficient. > > Actually, I was talking about the underlying order, because (at least > the last time I asked about this), pgsql doesn't do any intelligent > ordering under the covers. Because of that, I'd like to be able to order > based on the optimal ordering I was given (fixed width before variable, > then not null before null). Ah, you see that's where the confusion comes from, everyone else is not on about the physical ordering. I don't imagine core would consider an alter table command to reorder columns in the tuple store. -- Nigel J. Andrews
Bruno Wolff III <bruno@wolff.to> writes: > It is already a bad idea to use * in applications in case something does > change the order or number of columns. * is mostly going to be used in adhoc > queries where you care about saving some typing. Since this seems to have gone by without any contention I'll throw in 2c on the other side of this argument. I agree 100% with the logic but reach the opposite conclusion. One of the main goals of program design is to isolate the places that would have to be edited to accomplish any changes. If you explicitly list every or nearly every column in your queries, then whenever a new attribute is added you'll have to go through and add the new attribute to every query that fetched it. Whereas if you use select * and make the presentation code handle any columns that appear in the result then adding a new column can be done purely in the database. The query and the presentation layer can adapt without changes. Of course it's not always possible to achieve this 100%. Often the presentation layer wants to do specific formatting for each attribute and doesn't want to display every single attribute. But using select * means there's one fewer place that needs to be edited to effect the new column. This all depends on having a driver that gives you an API that lets you access columns by name rather than position though. -- greg
At 07:55 16.06.2003, Jim C. Nasby said:
--------------------[snip]--------------------
>99.999% of the time, if you put SELECT * into code, you should be strung
>up by your own entrails. But do you mean to tell me that when you're
>testing stuff on the command line you never, ever use SELECT *?
--------------------[snip]--------------------
Sure I do. But I don't really care about column ordering when doing a
SELECT * from the psql command line. If I care I usually have a dummy file
named "x" (because that's so damn short ;->), hack in my complex test
queries there, and do a \i x in psql. If I just want to see if something's
there (or not) I'm not interected in column order. Are you?
Basically as I understand it, SELECT * means "gimme all", not in any
particular order. How if at all is that defined in ANSI SQL?
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger wrote: > At 07:55 16.06.2003, Jim C. Nasby said: > >>99.999% of the time, if you put SELECT * into code, you should be strung >>up by your own entrails. But do you mean to tell me that when you're >>testing stuff on the command line you never, ever use SELECT *? > > Sure I do. But I don't really care about column ordering when doing a > SELECT * from the psql command line. If I care I usually have a dummy file > named "x" (because that's so damn short ;->), hack in my complex test > queries there, and do a \i x in psql. If I just want to see if something's > there (or not) I'm not interected in column order. Are you? > > Basically as I understand it, SELECT * means "gimme all", not in any > particular order. How if at all is that defined in ANSI SQL? SQL92: 7.9 <query specification> Syntax Rules 1) Let T be the result of the <table expression>. 2) The degree of the table specified by a <query specification> is equal to the cardinality of the <select list>. 3) Case: a) If the <select list> "*" is simply contained in a <subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>. b) Otherwise, 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 referenced in the ascending sequence of their ordinal position within T. It's that last sentence: "The columns are referenced in the ascending sequence of their ordinal position within T." As Tom pointed out earlier, it may not make sense purely from a relational point of view, but it is required by the standard. Given that it is required, it would be nice if the user could modify the ordinal position within T. Mike Mascari mascarm@mascari.com
At 16:21 16.06.2003, Mike Mascari said:
--------------------[snip]--------------------
>It's that last sentence:
>
>"The columns are referenced in the ascending sequence of their ordinal
>position within T."
>
>As Tom pointed out earlier, it may not make sense purely from a
>relational point of view, but it is required by the standard. Given
>that it is required, it would be nice if the user could modify the
>ordinal position within T.
Thanks for clearing this up! I had the perception column sequence was not
mentioned at all - too much time since I last read that stuff...
>TIP 7: don't forget to increase your free space map settings
Yes, but how much, and to what point? (not directed to you, but...)
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
> "The columns are referenced in the ascending sequence of their ordinal > position within T." > > As Tom pointed out earlier, it may not make sense purely from a > relational point of view, but it is required by the standard. Given > that it is required, it would be nice if the user could modify the > ordinal position within T. Now that there are some people that agree with me that this feature can be handy, i thought about how an alter table command should look like to re-order columns. how about ALTER TABLE <table> ALTER COLUMN <column> POSITION <int i> where 1<=i<=number of cols in table and the given column will be moved between the (i-1)-th and the i-th column within the table. if a admin-tool wants to redefine the order of the columns, it just has to submit one command for every column with ascending positions. is this acceptable? if i understood the other people correct, than we need such a command, because no user can access the pg_attribute table directly, right?
Greg Stark wrote: > Bruno Wolff III <bruno@wolff.to> writes: > >> It is already a bad idea to use * in applications in case something does >> change the order or number of columns. * is mostly going to be used in adhoc >> queries where you care about saving some typing. > > Since this seems to have gone by without any contention I'll throw in 2c on > the other side of this argument. > > I agree 100% with the logic but reach the opposite conclusion. One of the main > goals of program design is to isolate the places that would have to be edited > to accomplish any changes. > > If you explicitly list every or nearly every column in your queries, then > whenever a new attribute is added you'll have to go through and add the new > attribute to every query that fetched it. Disagreed. There can not be any "query that fetched it" because it didn't exist before. And if the surrounding application issuing that query didn't have that attribute before, what's it going to do with it now? It will hopefully just swallow it silently. > > Whereas if you use select * and make the presentation code handle any columns > that appear in the result then adding a new column can be done purely in the > database. The query and the presentation layer can adapt without changes. Ah ... we are talking about presentation code only. I have seen code that does a SELECT * from a table having multi-megabyte sized BLOB's only to check IF FOUND afterwards ... saving a few keystrokes can be expensive. In the case you have dynamic presentation code that handles this sort of thing, you are IMHO supposed to let it query a view. And this view has to be a separate view just for this "presentation" (view is a synonym for that, isn't it?). That way you are free to change the tables layout in whatever way you want to, from adding/renaming/dropping columns, splitting up the attributes into separate tables or changing data types ... whatever fits your "technical" needs. You can allways adjust the view so that the presentation will be OK again. > > Of course it's not always possible to achieve this 100%. Often the > presentation layer wants to do specific formatting for each attribute and > doesn't want to display every single attribute. But using select * means > there's one fewer place that needs to be edited to effect the new column. Using SELECT * means that there is one more place to carefully check for possible side effects. > > This all depends on having a driver that gives you an API that lets you access > columns by name rather than position though. You need an API that gives you access to the list of attribute names first, and then dynamically access those attributes. PL/pgSQL for one does not have this capability. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Ernest E Vogelsinger wrote: > Yes, but how much, and to what point? (not directed to you, but...) As much as required by the complexity and size of all the databases served by that postmaster. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Mon, Jun 16, 2003 at 06:49:50PM +0200, Sven K?hler wrote: > ALTER TABLE <table> ALTER COLUMN <column> POSITION <int i> > > where 1<=i<=number of cols in table and the given column will be moved > between the (i-1)-th and the i-th column within the table. I think that's fine. I think the normal case for doing this will be after you've added a new column, I think it would be pretty rare to want to re-order everything in the table. Of course if you did want to re-order everything in the table, it would be easier to just specify the list of column names in the new order you'd like to see them in. > if a admin-tool wants to redefine the order of the columns, it just has > to submit one command for every column with ascending positions. BTW, I've heard a lot of people talking about 'dragging columns around in select output', and I don't think that's a very good use case for this. The order of columns should be considered to be part of the table definition. Changing it should require the same privledges as adding or dropping a column. If a front-end wants to provide some wiz-bang interface customization on a per-user basis this isn't the mechanism that should be used. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
>>ALTER TABLE <table> ALTER COLUMN <column> POSITION <int i> >> >>where 1<=i<=number of cols in table and the given column will be moved >>between the (i-1)-th and the i-th column within the table. > > I think that's fine. I think the normal case for doing this will be > after you've added a new column, I think it would be pretty rare to want > to re-order everything in the table. Of course if you did want to > re-order everything in the table, it would be easier to just specify the > list of column names in the new order you'd like to see them in. perhaps we could also think about a ALTER TABLE <table> POSITIONS <column1>,<column2>,... but that is unnessary in my eyes. To issue 15 statements to re-order a complete table shouldn't be a problem. (assuming our table has 15 columns) >>if a admin-tool wants to redefine the order of the columns, it just has >>to submit one command for every column with ascending positions. > > BTW, I've heard a lot of people talking about 'dragging columns around > in select output', and I don't think that's a very good use case for > this. The order of columns should be considered to be part of the table > definition. Changing it should require the same privledges as adding or > dropping a column. If a front-end wants to provide some wiz-bang > interface customization on a per-user basis this isn't the mechanism > that should be used. Of course the described command should require the priviledge for modifying a table. I think we don't want any user to be abled to do that, because that will lead to chaos.
Hi, Bruno BAGUETTE wrote: > Hello, > > >>why not just order the output columns in alphabetical order? >>This is quite easy to implement and requires no additional >>storage. The output would be the same all the time and with all tools. > > > No, what we are talking about is to have the possibility to choose the > output order of the columns of a table (by adding a field in the > pg_attribute table). Sure, I did understand that. But I think it is quite pointless to burden the db or the app with such functionality. Just to have random ordered columns which do not correspondent to any physical ordering by the db. So it does not influence query performance to arrange the selected columns this or that order. So why confusing the user by pretending it has any meaning to order the columns? I think there are quite more praxis relevant problems to solve :) Regards Tino
Jan Wieck <JanWieck@yahoo.com> writes: > Disagreed. There can not be any "query that fetched it" because it didn't exist > before. And if the surrounding application issuing that query didn't have that > attribute before, what's it going to do with it now? It will hopefully just > swallow it silently. Sorry, dangling pronoun. "it" would have to be the entity, not the attribute. > > Whereas if you use select * and make the presentation code handle any columns > > that appear in the result then adding a new column can be done purely in the > > database. The query and the presentation layer can adapt without changes. > > Ah ... we are talking about presentation code only. I have seen code that does > a SELECT * from a table having multi-megabyte sized BLOB's only to check IF > FOUND afterwards ... saving a few keystrokes can be expensive. Well that's just one reason storing multi-megabyte objects in databases is usually a bad idea. Certainly it would be pretty unlikely and almost certainly a bad idea to have an existing entity that suddenly grows a multimegabyte attribute you hadn't thought of before. > In the case you have dynamic presentation code that handles this sort of thing, > you are IMHO supposed to let it query a view. And this view has to be a > separate view just for this "presentation" (view is a synonym for that, isn't > it?). That way you are free to change the tables layout in whatever way you > want to, from adding/renaming/dropping columns, splitting up the attributes > into separate tables or changing data types ... whatever fits your "technical" > needs. You can allways adjust the view so that the presentation will be OK > again. Bleagh. what a way to create tons of extra work for nothing. So now every time I add a new attribute to an existing entity I not only have to create the new column in the table, and create the new presentation code to handle the attribute, I not only have to check through all the queries that fetch the data for presentation and add the columns there, I now have to also check through all the views those queries use and add the columns there as well? The goal here is to avoid having to adjust ten million places every time you make a minor change to internal data. It's the whole reason ADTs and objects were invented. To avoid having to have every piece of code know the internals of every data type. What all the attributes of an entity are is just such an internal that should be hidden from code that doesn't need it. Unfortunately the best SQL can do on that front is SELECT * and even that doesn't work too well. But it's better than hard coding explicit lists of attributes everywhere throughout the application. > > Of course it's not always possible to achieve this 100%. Often the > > presentation layer wants to do specific formatting for each attribute and > > doesn't want to display every single attribute. But using select * means > > there's one fewer place that needs to be edited to effect the new column. > > Using SELECT * means that there is one more place to carefully check for > possible side effects. sure, except the "possible side effect" is the addition of one more attribute to the select list, which would be precisely what you're hoping to accomplish by adding the column. > > This all depends on having a driver that gives you an API that lets you access > > columns by name rather than position though. > > You need an API that gives you access to the list of attribute names first, and > then dynamically access those attributes. PL/pgSQL for one does not have this > capability. Well, to avoid hard coding the column names in the query you just need to be able to access the columns by name. To avoid hard coding them in the presentation layer then yes you would need to be able to retrieve the list of column names, which most interfaces do in fact provide. I don't think PL/PgSQL would be my first choice for writing presentation layer code in though. That said, avoiding it in the presentation layer is usually a lost cause anyways. -- greg
Really? What if two PgAdmin sessions want two different order? Presentation order should be done at the application level. JLL Tom Lane wrote: > > Bruno Wolff III <bruno@wolff.to> writes: > > Sven Köhler <skoehler@upb.de> wrote: > >> let's express it another way: we want to define the order of the columns > >> when they are shown in tools like phpPgAdmin or PgAdminII. > > > That is something that those tools should do then. It isn't really a > > postgres issue as it already provides ways for the tools to get > > information about columns of a table. > > Perhaps, but pg_attribute is the natural place to keep the column > ordering info. > > ISTM we talked months ago about adding an "attlognum" column to > pg_attribute, which would be separate from "attnum" (the physical column > position) and would be used by the backend only to determine the order > of SELECT * expansion. Admin tools could use this to determine column > display order too. It didn't get done, but it still seems like a > reasonable idea to me. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
> Really? > > What if two PgAdmin sessions want two different order? > Presentation order should be done at the application level. *sigh* ... The order of columns, the column type etc. is all part of table definition. if two guys want different column-orders this is just not possible - but if both have the possibility to agree and define one column ordering is better than force them to use what every postgresql decides (in most cases it is the chronological order, in which the columns have been created). and yes - column order of a select should be defined at application level - especially if the application uses indexes to access the columns - but some APIs offer access to the columns by using their name.
=?ISO-8859-1?Q?Sven_K=F6hler?= <skoehler@upb.de> writes:
> perhaps we could also think about a
> ALTER TABLE <table> POSITIONS <column1>,<column2>,...
You could invent a syntax that supports both use cases, along the lines
of
ALTER ... POSITION <i> <column1> [ , <column2> ... ]
with the meaning that the named columns are inserted sequentially between
positions i-1 and i, moving them from wherever they were, and leaving
all not-mentioned columns in their existing relative order. This
degenerates to the same as your first proposal if one column is named,
and at the other extreme allows all the columns to be re-ordered in one
command.
It could get a little confusing if some of the named columns previously
occupied positions less than <i>. I'd suggest the following
more-concrete specification:
1. <i> must be in the range 1 to (<number of columns in table> -
<number of columns named in statement> + 1).
2. After the ALTER, the named columns have ordinal positions <i>, <i+1>,
etc.
3. Any columns not named are placed into the remaining slots (1..i-1
and i+nnamedcols..ntablecols) in the same relative order they had
before.
regards, tom lane
On Mon, 16 Jun 2003, Jim C. Nasby wrote: > On Sat, Jun 14, 2003 at 06:55:36PM +0200, Bruno BAGUETTE wrote: > > Hello everybody, > > > > > The one thing we don't have that I think would be useful is a > > > way to re-order the columns in a table. Maybe it's just me, > > > but I tend to want column to appear in a specific order, and > > > the only way you can accomplish this today is by re-creating > > > the entire table. > > > > I agree with Jim, this would be a 'cool but not mandatory' feature ! :-) > > > > This feature is not mandatory since I avoid the use SELECT * FROM... > > (and I forbid the use of SELECT * to my subordinates). > > 99.999% of the time, if you put SELECT * into code, you should be strung > up by your own entrails. But do you mean to tell me that when you're > testing stuff on the command line you never, ever use SELECT *? I wouldn't go that far. I build updatable views, select * from them, cycle through the fields getting name / type and build generic forms to let the user edit / insert new records. It allows me to reuse the same basic chunk of code over and over. Of course, it's select * on a view, not a table, so I set the order when I create the view. Now, using select * and ASSUMING the order of the variables in your application code is a punishable offense, but as long as you determine the name / type of the fields after the select * it's not so bad.
I agree 100%. this makes very portable code, and usable libraries. scott.marlowe wrote: > On Mon, 16 Jun 2003, Jim C. Nasby wrote: > > >>On Sat, Jun 14, 2003 at 06:55:36PM +0200, Bruno BAGUETTE wrote: >> >>>Hello everybody, >>> >>> >>>>The one thing we don't have that I think would be useful is a >>>>way to re-order the columns in a table. Maybe it's just me, >>>>but I tend to want column to appear in a specific order, and >>>>the only way you can accomplish this today is by re-creating >>>>the entire table. >>> >>>I agree with Jim, this would be a 'cool but not mandatory' feature ! :-) >>> >>>This feature is not mandatory since I avoid the use SELECT * FROM... >>>(and I forbid the use of SELECT * to my subordinates). >> >> >>99.999% of the time, if you put SELECT * into code, you should be strung >>up by your own entrails. But do you mean to tell me that when you're >>testing stuff on the command line you never, ever use SELECT *? > > > I wouldn't go that far. I build updatable views, select * from them, > cycle through the fields getting name / type and build generic forms to > let the user edit / insert new records. > > It allows me to reuse the same basic chunk of code over and over. > > Of course, it's select * on a view, not a table, so I set the order when I > create the view. > > Now, using select * and ASSUMING the order of the variables in your > application code is a punishable offense, but as long as you determine the > name / type of the fields after the select * it's not so bad. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Mon, Jun 16, 2003 at 09:26:12PM +0200, Tino Wildenhain wrote: <snip> > I think there are quite more praxis relevant problems to solve :) ACK. I'd like to see some features, which can be good for distributed databases: * user defined OID spaces for serveral classes (and their derived classes) * automatic mtime field update (but only if data really changed) At the moment i'm doing this in the application (some not-too-small php code), but it would be nice if the RDBMS could do this, so this functionality could be provided directly through an SQL interface. For many applications i use an object-like table model: each table is derived from _inode, which defines an (cluster wiede) unique inode_id and an mtime field. The applications do not make direct queries, but access the db over an abstraction layer instead. This layer has definitions for each class (object type, properties, etc) and generates queries for actions like create, update, query, get,... It also implements some little caching. On create() the abstraction layer generates an query which fetches the inode_id from an sequence (this sequence has to be adjusted for each node in the cluster, so each node has its own inode_id space) and sets the mtime to current_timestamp(). On update() it also updates the mtime field. But also there's an raw-store mode, which does _not_ update the mtime field, but writes it as it comes from the user (this is necessary for syncing to avoid loops, since the post-out uses the mtime to find out which records to send to another node) I've tried to achieve this w/ rules, but i couldnt handle this (perhaps i'm too stupid ? ;-)) cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
>>I think there are quite more praxis relevant problems to solve :) > > ACK. i cannot agree because there are always more important problems than others. a mercedes is not such a beautiful car because of it's motor, but because of the beautiful well thought out features in the cockpit. Of course it would be a crappy car without a good motor - i hope you see, that details matter as much as the big problems.
Greg Stark wrote:
> Bleagh. what a way to create tons of extra work for nothing. So now every time
> I add a new attribute to an existing entity I not only have to create the new
> column in the table, and create the new presentation code to handle the
> attribute, I not only have to check through all the queries that fetch the
> data for presentation and add the columns there, I now have to also check
> through all the views those queries use and add the columns there as well?
>
> The goal here is to avoid having to adjust ten million places every time you
> make a minor change to internal data. It's the whole reason ADTs and objects
> were invented. To avoid having to have every piece of code know the internals
> of every data type. What all the attributes of an entity are is just such an
> internal that should be hidden from code that doesn't need it. Unfortunately
> the best SQL can do on that front is SELECT * and even that doesn't work too
> well. But it's better than hard coding explicit lists of attributes everywhere
> throughout the application.
You don't get the point, Greg. In all "applications" I have seen so far,
the number of places where the code analyzes the attributes actually
returned from a SELECT * for display purposes vs. the number of places
where the application code needs explicit fields from one or more tables
tends to be highly in favor for the latter. You know any example to the
contrary out of the top of your head?
If one needs to add attributes that often and has that many places in
his code that need to look what they got after something else blindly
selected whatever that table looks like today ... IMHO that's an
indicator for two things.
1. He doesn't know that a developer is supposed to write a concept
and a specification before dragging and dropping around like a
berserk in some graphical design toy.
2. His code prooves that he doesn't remember what he coded last
week and all the reusability of code, OO was supposed to give
us, is lost in the growing address space of his applications.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
[This is fairly off-topic now. I just wanted to make it clear that there were both pros and cons to "select *" and people shouldn't assume they can just dismiss things as bad practice based on simplistic rules-of-thumb. I don't think we should continue this thread much longer.] Jan Wieck <JanWieck@Yahoo.com> writes: > You don't get the point, Greg. In all "applications" I have seen so far, the > number of places where the code analyzes the attributes actually returned from > a SELECT * for display purposes vs. the number of places where the application > code needs explicit fields from one or more tables tends to be highly in favor > for the latter. You know any example to the contrary out of the top of your > head? In my experience there are usually a handful of crucial attributes that are integral to the design. These are often used in where clauses and such and obviously need to be well thought out from the start. Of the other attributes they mostly tend to evolve with the application and the use of the database. These are fields that turn out to be needed for some particular application need that may be a new feature or may be a design deficiency. In fact I specifically try to postpone adding such attributes until the relevant portions of the application is being written. I find when designed in advance such non-structural database attributes more often than not turn out to be completely inappropriate and need to be replaced. Or often a lot of work is spent making them infinitely flexible because the relevant application needs aren't fully thought out. Or worse, the table structure is delayed for a long time until every facet of the design is ironed out, preventing progress on even the basic structure of the code. You asked if I know of any examples, well, sure. Just the other day I added a new column to a table to handle a new attribute needed to solve a new client requirement. It was a simple attribute, just a free-form text field that had to be displayed in an existing tabular display of data. If the query had been written using "select *" I would have been able to add the attribute to the presentation by editing the template data file. No code at all would have had to be edited. As it turned out the query hadn't been written this way (and couldn't have been, because of a detail I haven't mentioned). I wasted about 20 minutes tracking down the bug that the field wasn't showing up despite it being in the template. Eventually I was able to track down the query that fed that template and found the missing column in the select list. In an ideal world the template data files shouldn't even be under the control of the same person as the application code and database structure. Adding and removing attributes from amongst the existing columns should be a simple operation for a graphic artist to do on the static data files without any modifications in the database. This is in the early stage of the project. I would say that as a project ages more and more of the changes are of this form. In past projects, Over the course of the life of a project easily more than half of the attributes have been added long after the initial design. -- greg
Okay, so say now there's a attlognum column in pg_attribute. How is this going to interact with rowtype variables in plpgsql? Just thought I'd raise this seeing as there's already an issue with attisdropped. -- Nigel J. Andrews
> > Presentation order should be done at the application level. I agree. Use a VIEW for the presentation! If you use a VIEW for the presentation, then the presentation code can use SELECT * from that view. The code will never haveto be changed. The VIEW is the presentation - Change the VIEW, and the presentation is changed. If you use SELECT * from a table, then you might have to change the code later if you later decide you want to join in othertables. With a VIEW this is not a problem. If you want the columns from table "customers" in one order for one report and another order for another report, then a SELECT* from table will never work. ALTER TABLE ...POSITION.. won't help either. With a VIEW this is not a problem. If you want the rows from table "customers" ordered by customer_name for one report and by sales_limit for another report,then a SELECT * from table will never work because you don't know (on the application level) what columns there willbe in the table and what they mean. With a VIEW this is not a problem. If you want the column "very_sensitive_personal_opinions_about_this_customer" not to show up in every report, then a SELECT* from table will never work. With a VIEW this is not a problem. ... and so on... Sure, you (may) have to update the view when you add or drop a column. However, that is *easy* to do, and you get all ofthe above mentioned positive effects. Conclusion: Use SELECT * FROM <view> The network is the computer. The view is the presentation. /Mattias Tip 4711: The VIEW is the presentation
Greg Stark wrote: > > [This is fairly off-topic now. I just wanted to make it clear that there were > both pros and cons to "select *" and people shouldn't assume they can just > dismiss things as bad practice based on simplistic rules-of-thumb. I don't > think we should continue this thread much longer.] I just wanted to make clear that there are much less cases where a "SELECT *" is a good thing at all and that it should be used carefull. And sorry, you can't make a technical statement and ask "shut up" in the same mail just to have the last word. > In my experience ... The problem is not that software evolves, the problem is the development process, or better the lack of that. Why did you need 20 minutes to hunt down that select? Missing some documentation? Missing some structure in the code? The reason why new features are added to PostgreSQL only through release cycles, with lots of peer review, full BETA test phase and everything, is not because we all are a bunch of hobby programmers who don't know how real world software projects work. Fortunately in our case there is no paying customer pushing a stupid manager around and forcing every day spec changes to violate good development practices. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> > > Presentation order should be done at the application level. > > I agree. > > Use a VIEW for the presentation! > Sorry, but I don't fully agree with you. If I have to add a new column in a table, this column will appear in the end of the table. What we are talking about (as I understand) is to have the possibility to order the columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table> would use that order to display the columns. We are not talking of changing columns order for each kind of SQL query. In that case, I agree that views are usefull and when we want to display several tables in one "pseudo-table" (a view) and to have less big queries. I really think that column ordering (ALTER TABLE ...POSITION..) is very interesting and will allow users to avoid loosing time when they have to create a new temporary table each time they have to add a new column inside (not at the end of) a table, and rename the table after deleting the old table... :-) Regards, --------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
> Sorry, but I don't fully agree with you. If I have to add a new column > in a table, this column will appear in the end of the table. What we are > talking about (as I understand) is to have the possibility to order the > columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table> > would use that order to display the columns. yes. it is a feature that not many DBMS support (i know only of MySQL which supports inserting a column at a defined splace - and don't blame me - i don't like MySQL either) - it is a feature that's missing in many DBMS too. > We are not talking of changing columns order for each kind of SQL query. > In that case, I agree that views are usefull and when we want to > display several tables in one "pseudo-table" (a view) and to have less > big queries. I don't know about PostGreSQL, but views can be slower than normal queries - especially when you don't do a "select * form view" but add a some where-clauses, joins etc.
> > > > Presentation order should be done at the application level. >> > > I agree. > > Use a VIEW for the presentation! > > Sorry, but I don't fully agree with you. If I have to add a new column > in a table, this column will appear in the end of the table. What we are > talking about (as I understand) is to have the possibility to order the > columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table> > would use that order to display the columns. Yes, I understand that, but I don't understand what the benefits would be. What use is it to have the columns in a defined order when you do (SELECT * FROM table)? 1. In a "normal" app, you would want to know what the data in the column *means*. Adding a column "kexchoklad" to the "customers"table would not be of any good, regardless of it's position relative to other columns. You would never use "SELECT*". You would SELECT only the columns that matter to this specific part of the app. 2. In the case of a report generator: You would probably not want *every* column from "customers" for a report... How canyou print a report without knowing how many columns you'll get? without knowing what they contain? what they mean? Whereshould you print "kexchoklad"? After the customer name? Just before last_years_sales? In this case you would never useSELECT *. You would probably want to join in other tables too. 3. In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a SELECT * could be of any use...!! Do a SELECT *, and print it out just to get an overview of what's in the table. In case #3: Ok, this is the relevant case. But do you really want to rearrange the table internally just for this specialcase?? seems like a lot of programming and potential problems just for one very special case... especially since itcan be done quick and easy with a view...! Do you even need a view? If you do a quick and ugly report, do you even careabout the column position of "kexchoklad"?? Can you tell me an example of a situation when the column position really matters? > We are not talking of changing columns order for each kind of SQL query. > I really think that column ordering (ALTER TABLE ...POSITION..) is very > interesting and will allow users to avoid loosing time when they have to > create a new temporary table each time they have to add a new column > inside (not at the end of) a table, and rename the table after deleting > the old table... But *why* would anyone care about the position of the column? Except from a cosmetical point of view... Why all the hasslewith temp tables and stuff, just to put the column in a specific position in the table definition? Because it looksneat when you do "\d table" in psql?? I think the original poster was talking about the physical layout of the row, and that it would make some selects more efficient.Exactly how that would be accomplished was not explained. Then someone started talking about the logical layout (in the specific case of SELECT *), and printing reports. The physical layout should probably be handled by pg internally, without any interference from users. The logical layout, well... i just can't see why it would matter at all? /Mattias
On 17 Jun 2003 at 15:16, Mattias Kregert wrote: > Can you tell me an example of a situation when the column position really matters? I would like to rephrase that question. Can you tell me an example of a situation when the column position really matters given 1) You can specify column order in sql select statement 2) most basic interface to postgresql, libpq supports search on a result tuple by field number or field name? If the client library you are using does not support this, you can hack it. Libpq supports it for sure. I have been deleting all mails on this thread and really wonder what was in there in all those mail. IMO the thread is not worth living this long but I may be wrong, given I haven't read all those mails.. Bye Shridhar -- "Trust me": Translation of the Latin "caveat emptor."
Mattias Kregert wrote: >>>>>Presentation order should be done at the application level. >>>> >>>I agree. >>>Use a VIEW for the presentation! >> >>Sorry, but I don't fully agree with you. If I have to add a new column >>in a table, this column will appear in the end of the table. What we are >>talking about (as I understand) is to have the possibility to order the >>columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table> >>would use that order to display the columns. > > > 3. In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a SELECT * could be of any use...!! > I have been following this thread with great interesting and perplexity. I have yet to understand the reasoning behind this proposed addition. It seems useful only for SELECT * yet most posts say that "SELECT *" is bad in an app. Others say that if SELECT * is used then the app has to look for the proper column(s) anyway so ordering is not important. As stated in the parent post from Mattias Kregert (with whom I completely agree with), SELECT * is generally always a quick-n-ugly check of the table. Surely us humans can adapt to the column positions for checking tables once in a while. And what if an application, expecting a pre-defined order, receives a column in a position that it doesn't expect? Wouldn't it still be better to define the column order in the SELECT statement or just look for the column it wants in the table information? Insofaras rearranging the internal table is concerned, I don't believe the pg people had that intent in mind at all. It may have been the intent of the original poster, but I think most everyone agrees that the back-end knows far better than us humans what is more optimal for table layout. It just seems that this is extra work for little benefit. Applications that allow people to move columns for cosmetics should deal with the storage of that application specific configuration data. If you use multiple applications that permit customization of column positions then it falls upon each application to store the configuration data as it sees fit. Shane
> I have been following this thread with great interesting and > perplexity. I have yet to understand the reasoning behind this proposed > addition. It seems useful only for SELECT * yet most posts say that > "SELECT *" is bad in an app. Others say that if SELECT * is used then > the app has to look for the proper column(s) anyway so ordering is not > important. As stated in the parent post from Mattias Kregert (with whom > I completely agree with), SELECT * is generally always a quick-n-ugly > check of the table. Surely us humans can adapt to the column positions > for checking tables once in a while. And what if an application, > expecting a pre-defined order, receives a column in a position that it > doesn't expect? Wouldn't it still be better to define the column order > in the SELECT statement or just look for the column it wants in the > table information? I don't want to abled to define the column-order just because my "select *" would look better - it's just that a "select *" should also show the defined column-order if there is any. Defining the column-order is just an organisational task. It is just like having good identifier names in your program-code or like tidying up your desk - i don't tidy up my desk that often, but i want a certain tidiness in my database. In addition, postgresql doesn't offer anything to change a column-definition. So although your columns are in the logical order you like when you create a table, your logical order will be broken if you add a column that you've forgotton or have to change a columns type by copying the data to a new column. In order to do something equivalent to a column definition change (the stuff this thread was about initially) you have to create a new column with the desired type, copy data, delete the old column _and_ move the new column to the place the old column was. In addition, beeing abled to define the column order is a step into the direction of a more complete ALTER TABLE command - something the most DBMS are lacking. MySQL is abled to insert a column at a certain position, but isn't abled to re-arange columns - this might be due to the fact, that MySQL only knows the physical order of the columns. This is something we are not expecting from postgresql - since physical order doesn't matter much from the user perspective and might be optimized by postgresql internally. Having a defined column-order is a good thing (would be a basic requirement to optimize the physical column-order without modifying the table layout) and to be abled to modify that defined column ordering is some kind of service for the user.
I personally agree with this. I put the fields in the create statement in a particular order to help with understanding the design. Now, if the DB wantsto put them in whatever STORAGE order it wants, fine, as long as it displays in the same order I created it. A possible, probably completely non standard solution to both problems would be a COMBINATION of a select list and '*': SELECT (col1, col2, * ) from TABLE1; Certain tables are ordered, the rest are just appended in order of definition behind it. Mattias Kregert wrote: >>>>>Presentation order should be done at the application level. >>> >>>I agree. >>>Use a VIEW for the presentation! >> >>Sorry, but I don't fully agree with you. If I have to add a new column >>in a table, this column will appear in the end of the table. What we are >>talking about (as I understand) is to have the possibility to order the >>columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table> >>would use that order to display the columns. > > > Yes, I understand that, but I don't understand what the benefits would be. > > What use is it to have the columns in a defined order when you do (SELECT * FROM table)? > > 1. In a "normal" app, you would want to know what the data in the column *means*. Adding a column "kexchoklad" to the "customers"table would not be of any good, regardless of it's position relative to other columns. You would never use "SELECT*". You would SELECT only the columns that matter to this specific part of the app. > 2. In the case of a report generator: You would probably not want *every* column from "customers" for a report... How canyou print a report without knowing how many columns you'll get? without knowing what they contain? what they mean? Whereshould you print "kexchoklad"? After the customer name? Just before last_years_sales? In this case you would never useSELECT *. You would probably want to join in other tables too. > 3. In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a SELECT * could be of any use...!! Do a SELECT *, and print it out just to get an overview of what's in the table. > > In case #3: Ok, this is the relevant case. But do you really want to rearrange the table internally just for this specialcase?? seems like a lot of programming and potential problems just for one very special case... especially since itcan be done quick and easy with a view...! Do you even need a view? If you do a quick and ugly report, do you even careabout the column position of "kexchoklad"?? > > Can you tell me an example of a situation when the column position really matters? > > > >>We are not talking of changing columns order for each kind of SQL query. >>I really think that column ordering (ALTER TABLE ...POSITION..) is very >>interesting and will allow users to avoid loosing time when they have to >>create a new temporary table each time they have to add a new column >>inside (not at the end of) a table, and rename the table after deleting >>the old table... > > > But *why* would anyone care about the position of the column? Except from a cosmetical point of view... Why all the hasslewith temp tables and stuff, just to put the column in a specific position in the table definition? Because it looksneat when you do "\d table" in psql?? > > I think the original poster was talking about the physical layout of the row, and that it would make some selects moreefficient. Exactly how that would be accomplished was not explained. > Then someone started talking about the logical layout (in the specific case of SELECT *), and printing reports. > The physical layout should probably be handled by pg internally, without any interference from users. > The logical layout, well... i just can't see why it would matter at all? > > > /Mattias > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Shane Dawalt <shane.dawalt@wright.edu> writes:
> I have been following this thread with great interesting and
> perplexity. I have yet to understand the reasoning behind this proposed
> addition.
The original takeoff point was the observation that you couldn't build
"ALTER COLUMN TYPE" out of the existing spare parts: you can make a new
column, load it with the old data, and drop the old column (with a
rename somewhere along the line) ... but this leaves you with the new
column at the end, so it's not a fully transparent substitution.
> Insofaras rearranging the internal table is concerned, I don't
> believe the pg people had that intent in mind at all. It may have been
> the intent of the original poster, but I think most everyone agrees that
> the back-end knows far better than us humans what is more optimal for
> table layout.
Indeed, but under the constraints of the SQL spec, the backend is not
free to change the visible column order for implementation reasons.
If we were to decouple logical and physical order as this thread is
suggesting, then the door *would* be open to perform that sort of
optimization.
regards, tom lane
> Insofaras rearranging the internal table is concerned, I don't believe > the pg people had that intent in mind at all. It may have been the > intent of the original poster, but I think most everyone agrees that the > back-end knows far better than us humans what is more optimal for table > layout. BTW: i never had that in mind. i don't even care about the physical or internal column-order.
On Tue, Jun 17, 2003 at 10:05:48AM -0400, Shane Dawalt wrote: > Insofaras rearranging the internal table is concerned, I don't > believe the pg people had that intent in mind at all. It may have been > the intent of the original poster, but I think most everyone agrees that > the back-end knows far better than us humans what is more optimal for > table layout. Again, yes, it would be nice if pgsql would order things most efficiently in the back-end, but it doesn't. I don't care at all about how stuff comes out in SELECT *. I DO care about how it's stored in the tuple. Much, if not most, of the work required to make this change happen would be required to de-couple presentation from tuple ordering anyway, and that seems to be one feature everyone agrees would be good. If the de-coupling is going to be added, might as well add a nice feature that won't really hurt anything at the same time. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
hi,
so here's a small summery of all the we wrote about changing a column's
definition and/or column-ordering.
column definition change:
- changing a column's defintion is possible in a few situations, but the
main problem is the conversion of the data from one type into another.
- chaning a column's definition is not part of the the SQL-Standard an
therfor is not well defined - even if other DBMS implement such a
feature, they all do it in a different way - so there is no practical
standard
- this feature will not be implemented in near future, and a macro or
something within a tool would also be sufficient (i asked the
pgadmin-team and they promised to think about it - what ever that means)
- still there is some problem:
even if there is a macro within a tool to change a columns definition,
it needs to create a new column which will appear at the end of the
column-list. so one small feature is still needed: user-defined column order
user defined column order:
let's define two things:
- physical column order
the column order with which the column-data is ordered within a table
row when writing it to disk
- logical column order
the column order that postgresql presents to it's clients
it does not need to match te physical order. it is currently equal to
the creation order and also equal the physical order (as far as i know).
WHY?
- defining the column order of a table is part of the creation of the
table. after that, the column order can only be changed by deleting and
creating new columns or by creating new complete new table
- in order to have a full equivalent of changing a column definition,
setting a columns position is required
- many users create a table with a certain column order to help
themselfs or just to keep a certain tidiness, adding a column or
changing it's type would break that order
WHY NOT?
- having a a logical order different from the physical order only
affects "select *" and "select *" should not be used.
- views could be uses to achieve a certain column order
- it's not an important feature and needs a new statement, because users
cannot update the pg_attribute table directly
- any admin-tool could store the its own column ordering in separate tables
WHY ANYWAY?
- having a logical column order is an advantage over other DBMS
- the logical column order should affect "select *" to avoid confusion
("select *" is not the reason the have a logical column order)
- beeing unabled to define the column order makes it look like chaos
after some years. table recreation is needed to compensate that. that's
unacceptable
- the tools will never agree how they store the column order information
if postgresql doesn't provide any way to do that.
SUGGESTIONS:
- add a column to the pg_attribute table to store the logical order
- make "select *" use that new column instead of the old physical order
column
- add a new command to the query interpreter
1. ALTER TABLE <table> ALTER COLUMN <column> POSITION <i>
2. ALTER TABLE <table> POSITIONS <i> <column>,<column>,...
the first statement would be sufficient to do all things, the second is
just a more powerfull shortcut.
ADVANTAGES:
- if there is any advantage in having this or that physical order,
postgresql could optimize the physical order and keep the logical
- MySQL supports inserting a new column at a given position. MySQL
changes the physical order of the columns. Postgresql can do better by
optimizing the physical order and maintaining the logical order separatly.
> You could invent a syntax that supports both use cases, along the > lines of > > ALTER ... POSITION <i> <column1> [ , <column2> ... ] This idea is great, although the statement ALTER TABLE <table> POSITION <i> <column>,<column>,... might make the task to maintain the pg_attribute table more complicated than the simple statement ALTER TABLE <table> ALTER COLUMN <column> POSITION <i> which can be transformed into 2 update-statements i think. perhaps it would be simpler to define a statement like ALTER TABLE <table> POSITIONS <column1> <i1>, <column2> <i2>, ... which just means the following: ALTER TABLE <table> ALTER COLUMN <oclumn1> POSITION <i1> ALTER TABLE <table> ALTER COLUMN <oclumn2> POSITION <i2> we wouldn't have such strong/complicated contraints for each <i>, because each <i> can be >=1 and <= the column-count. i don't know what i'd like most, but although your last suggestion looks great, it makes it hard to estimate what's the result.
Added to TODO:
o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
have SELECT * and INSERT honor such ordering
---------------------------------------------------------------------------
Tom Lane wrote:
> =?ISO-8859-1?Q?Sven_K=F6hler?= <skoehler@upb.de> writes:
> > perhaps we could also think about a
> > ALTER TABLE <table> POSITIONS <column1>,<column2>,...
>
> You could invent a syntax that supports both use cases, along the lines
> of
>
> ALTER ... POSITION <i> <column1> [ , <column2> ... ]
>
> with the meaning that the named columns are inserted sequentially between
> positions i-1 and i, moving them from wherever they were, and leaving
> all not-mentioned columns in their existing relative order. This
> degenerates to the same as your first proposal if one column is named,
> and at the other extreme allows all the columns to be re-ordered in one
> command.
>
> It could get a little confusing if some of the named columns previously
> occupied positions less than <i>. I'd suggest the following
> more-concrete specification:
>
> 1. <i> must be in the range 1 to (<number of columns in table> -
> <number of columns named in statement> + 1).
> 2. After the ALTER, the named columns have ordinal positions <i>, <i+1>,
> etc.
> 3. Any columns not named are placed into the remaining slots (1..i-1
> and i+nnamedcols..ntablecols) in the same relative order they had
> before.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
> Added to TODO: > > o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2]; > have SELECT * and INSERT honor such ordering Great to hear that. Thx.
On Sat, Jun 14, 2003 at 07:33:19PM +0200, Sven K?hler wrote:
>
> "select *" should refelect the cosmetical order of the columns.
Why? You asked for everything, and specified no order.
> "select *" could be tranformed into something like "select col1, col2,
> ..." according to the cosmetical order that's defined.
What's wrong with CREATE VIEW if this is so important?
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Wed, 25 Jun 2003 15:29:20 -0400, Andrew Sullivan
<andrew@libertyrms.info> wrote:
>On Sat, Jun 14, 2003 at 07:33:19PM +0200, Sven K?hler wrote:
>>
>> "select *" should refelect the cosmetical order of the columns.
>
>Why? You asked for everything, and specified no order.
AFAICS it's a matter of standard conformance. SLQ92 says
in 4.8 Columns:
A column is described by a column descriptor. A column descriptor
includes:
[...]
- the ordinal position of the column within the table that con-
tains the column.
and in 7.9 <query specification>:
3) Case:
a) [deals with EXISTS]
b) Otherwise, 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.
4) The <select sublist> "<qualifier>.*" for some <qualifier> Q is
[similar to 3b]
Servus
Manfred