Re: [GENERAL] change natural column order
Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: Hello everyone, When I create a table and later on (say, because customers want to store extra info) add a column, like this: create table test (lastfield varchar); alter table test add column firstfield varchar; is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. I'd rather not use views, allthough I know this is the right way, because it would make the application a lot more complex. Can I dive into some system tables and change the natural order? Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. If you want to have a given ordering, why not just specify your column names in that order in your statements? Or just refer to them by column name if your host language allows it. I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) Regards Tino ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] change natural column order
Tino Wildenhain zei: Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: Hello everyone, When I create a table and later on (say, because customers want to store extra info) add a column, like this: create table test (lastfield varchar); alter table test add column firstfield varchar; is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. I'd rather not use views, allthough I know this is the right way, because it would make the application a lot more complex. Can I dive into some system tables and change the natural order? Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. Hi Tino, Yes, I know it's not very standard and certainly not in the spirit of relational db's If you want to have a given ordering, why not just specify your column names in that order in your statements? Or just refer to them by column name if your host language allows it. The frondend functions are made so they accept any query (select *) and find out or themselves how to handle things. I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I'll have a look at the system tables (that's where the answer must be) but maybe someone who has done this can save me the time... Thanks! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] change natural column order
Hi, Am Dienstag, den 30.11.2004, 11:31 +0100 schrieb Joolz: ... If you want to have a given ordering, why not just specify your column names in that order in your statements? Or just refer to them by column name if your host language allows it. The frondend functions are made so they accept any query (select *) and find out or themselves how to handle things. SELECT * is almost always bad style. It shouldnt be so hard to write the columns you need even in generic queries. And if you have so smart frontend functions they can always read the column names to find out - while naming them explicit in the select clause saves a lot of hassle here too. I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I'd better find out why :-) And change just this requirement :-) Pro: it also makes your application more db agnostic. I'll have a look at the system tables (that's where the answer must be) but maybe someone who has done this can save me the time... And next time you want to change the internals of the DB to not have to write an ORDER BY into your queries? :-) Regards Tino ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] change natural column order
Joolz wrote: I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. You have my sympathies, but you knew you weren't supposed to rely on the ordering. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
Richard Huxton zei: Joolz wrote: I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. Got it: http://archives.postgresql.org/pgsql-hackers/2003-11/msg00869.php You have my sympathies, but you knew you weren't supposed to rely on the ordering. Although I agree it sounds a bit weird, I saw someone mentioning that column ordering is a part of ANSI-SQL. Anyway, I'll have a look at attnum, see what it can do for me. Thanks everyone! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
Hi, Joolz, you already got quite a few answers, that the frontend is probably not properly designed, if it relies on a certain column ordering. I agree completely with that. However your question got me curious, and I've digged around a bit in the system tables. You might be interested in my findings. See below. Citing Joolz [EMAIL PROTECTED]: I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I'll have a look at the system tables (that's where the answer must be) but maybe someone who has done this can save me the time... If you do: set search_path=information_schema; \d columns and look at the Columns defined, you'll find a column called ordinal_position, which incidentally corresponds to the position of the columns on output. If you dig a bit further and look at the definition of the columns view, you'll find, that this column comes from a column attnum in pg_attribute. As database superuser, you can actually change the values of attnum, however doing so results in: ERROR: invalid memory alloc request size 4294967295 on queries on the tables for which you changed attnum. So: 1.) obviously PostgreSQL does not like it at all (at least on my platform, which is OpenBSD 3.6) 2.) I wouldn't risk messing with a system table, which I can only write to if I'm superuser if I don't completely understand what's happening behind the scenes (at least not for production use). 3.) changing that behaviour is probably a lot more work than changing the frontend. Regards, Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
Tino Wildenhain wrote: Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. This is not true. Columns have an order. You can do INSERTs without specifying the columns. The values won't be inserted randomly but in their order. Changing the order of the columns is not a frontend question but logically. (Some) people would like to see relevant columns near to each other, even with an admin program. I would welcome some alter table column order feature. Btw human beings like changing things. Mage ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] change natural column order
Daniel Martini zei:
Hi,
Joolz, you already got quite a few answers, that the frontend is
probably
not properly designed, if it relies on a certain column ordering. I
agree
Hi Daniel,
Well, I made the frontend myself, so... :)
There is a reason that I made it this way, I have a database with a
lot of different tables and I wanted the frontend to be as versatile
as possible, so I wouldn't have to write PHP frontend functions for
each table or change the SELECT statements that generate the data
for the frontend each time a column is added (and this will happen).
So my application does things like this (semi-code):
$exclude_columns = {oid, audit_column_one, audit_column_two};
function one() {
$sql = select * from fubar;
two($sql);
}
function two() {
// make array from $sql
// remove elements that exist in $exclude_columns
// show array
}
completely with that. However your question got me curious, and I've
digged
around a bit in the system tables. You might be interested in my
findings.
See below.
BTW I found out that my questions is not as weird as I expected it
to be. MySQL can do it (AFTER clause), Firebird too, and without a
doubt others like Oracle and DB2 too.
Citing Joolz [EMAIL PROTECTED]:
I agree. Only I think this wouldn't require new functionality, I
have a gut feeling that this is possible as it is. Now only find
out
how :)
I'll have a look at the system tables (that's where the answer
must
be) but maybe someone who has done this can save me the time...
If you do:
set search_path=information_schema;
\d columns
and look at the Columns defined, you'll find a column called
ordinal_position,
which incidentally corresponds to the position of the columns on
output. If
you dig a bit further and look at the definition of the columns
view, you'll
find, that this column comes from a column attnum in pg_attribute.
As
database superuser, you can actually change the values of attnum,
however
doing so results in:
ERROR: invalid memory alloc request size 4294967295
on queries on the tables for which you changed attnum. So:
1.) obviously PostgreSQL does not like it at all (at least on my
platform,
which is OpenBSD 3.6)
2.) I wouldn't risk messing with a system table, which I can only
write to
if I'm superuser if I don't completely understand what's
happening
behind the scenes (at least not for production use).
3.) changing that behaviour is probably a lot more work than
changing the
frontend.
Yes, if I understand all the threads correctly, attnum is somehow
bound to the physical location of the data it represents. That makes
it almost impossible to fiddle around with it. Someone proposed to
make an extra field attpos, but it doesn't look like this will be
happening soon.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] change natural column order
SELECT * is almost always bad style. It shouldnt be so hard to
Why ?
Many languages, including PHP, have associative arrays, so you should
just use array[column_name] instead of array[column_number]. This is what
I do, all the time.
For instance, in Python :
* The wrong way :
cursor.execute( SELECT name, address, zipcode FROM people WHERE blah )
data = cursor.fetchone()
name = data[0]
address = data[1]
zipcode = data[2]
This is BAD because :
- When your SELECT has more than a few columns, you have to be really
really careful about the order, and one day you'll mess it up and it'll
bite you. Same thing with parameters by number in printf !
- When you add a column, you have to go through all the SELECTs in your
app that are not auto-generated
- Including all the columns slows down query generation and query parsing.
* The Very wrong way :
cursor.execute( SELECT * FROM people WHERE blah )
data = cursor.fetchone()
name = data[0]
address = data[1]
zipcode = data[2]
This is BAD because, when your table structure changes, your application
breaks.
* How I do it :
cursor.execute( SELECT * FROM people WHERE blah )
data = cursor.dictfetchone()
name = data['name']
address = data['address']
zipcode = data['zipcode']
or :
for key, value in data.items():
print key, =, value
or instanciate a class and set its attributes:
result = myclass()
for key, value in data.items():
setattr( result, key, myclass.type_converter[key](value) )
The last being how a decent DB library would do it.
I find this a lot better, because :
- no need to generate and then parse long queries with all the columns
- no worries about column order or adding columns
- raises an exception if a column misses or has the wrong name
- a lot simpler
- a lot more explicit
- you can auto-cast to and from the DB if your class has a table of type
converters indexed on the column name
- etc...
Also, in my case, it eases query generation a lot, I use the same code
for many tables.
You can do this in PHP I believe with associative arrays...
Now, to prove the point, take the following PHP code ripped out of the
osCommerce (which I don't consider an example of good programming, but
it's a good example here). It builds a SELECT wiht various parameters.
Now, tell me, if you access columns according to their number in the
result, what is the column number for the products_description ?
If you access columns by their name, then it's just
$result['products_description']
code class=spaghetti
$select_column_list = '';
for ($i=0, $n=sizeof($column_list); $i$n; $i++) {
switch ($column_list[$i]) {
case 'PRODUCT_LIST_MODEL':
$select_column_list .= 'p.products_model, ';
break;
case 'PRODUCT_LIST_NAME':
$select_column_list .= 'pd.products_name,
pd.products_description, ';
break;
case 'PRODUCT_LIST_MANUFACTURER':
$select_column_list .= 'm.manufacturers_name, ';
break;
case 'PRODUCT_LIST_QUANTITY':
$select_column_list .= 'p.products_quantity, ';
break;
case 'PRODUCT_LIST_IMAGE':
$select_column_list .= 'p.products_image, ';
break;
case 'PRODUCT_LIST_WEIGHT':
$select_column_list .= 'p.products_weight, ';
break;
}
}
// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
if (isset($HTTP_GET_VARS['filter_id'])
tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
$listing_sql = select . $select_column_list . p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
s.specials_new_products_price, p.products_price) as final_price from .
TABLE_PRODUCTS . p, . TABLE_PRODUCTS_DESCRIPTION . pd, .
TABLE_MANUFACTURERS . m, . TABLE_PRODUCTS_TO_CATEGORIES . p2c left
join . TABLE_SPECIALS . s on p.products_id = s.products_id where
p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = ' . (int)$HTTP_GET_VARS['manufacturers_id'] . ' and
p.products_id = p2c.products_id and pd.products_id = p2c.products_id and
pd.language_id = ' . (int)$languages_id . ' and p2c.categories_id = ' .
(int)$HTTP_GET_VARS['filter_id'] . ';
} else {
// We show them all
$listing_sql = select . $select_column_list . p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
Re: [GENERAL] change natural column order
Richard Huxton [EMAIL PROTECTED] writes: I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. Yup, that was exactly the conclusion. Too much work and too much risk of introducing bugs (by using the wrong one of logical and physical column number in any given place). You really have to drop and recreate the table if you want to reorder the columns. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] writes: I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. Yup, that was exactly the conclusion. Too much work and too much risk of introducing bugs (by using the wrong one of logical and physical column number in any given place). You really have to drop and recreate the table if you want to reorder the columns. This can be hard if you have foreign keys. I used dump, edit, and restore the whole database in the past. Mage ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
Pierre-Frédéric Caillaud [EMAIL PROTECTED] writes: SELECT * is almost always bad style. It shouldnt be so hard to Why ? Many languages, including PHP, have associative arrays, so you should just use array[column_name] instead of array[column_number]. This is what I do, all the time. This is another religious issue you'll find people pretty adamant on both sides. I tend to prefer to use SELECT * because it reduces repetition and improves modularity. There are fewer places in the code that need to know about a new column being added to a table (or expression to a query) and fewer places that need to know about a new column (or expression) being needed in the final result. [I am assuming you use look up columns by name. To me it seems the only reasonable approach for all but the simplest cases] However many databases and interfaces have some pretty strong practical problems that result from using it. So it's pretty standard DBA rule-of-thumb material to discourage its use. Oracle had serious problems dealing with prepared statements and views when new columns were added. I think they've mostly resolved those issues. The only problem I've run into with Postgres is that there's no way to *remove* a column from a list of columns without listing all the non-removed columns. And there's no way to disambiguate if you add a second column by the same name. So you have some situations where you can't add an expression with the correct name without explicitly listing every other column. There may be performance implications for having more columns than necessary in a select list as well. I wouldn't worry too much about this for reasonable sizes but when you start doing joins against many tables, some of which could be quite wide, and you don't need many of the columns being included then the select * could be slowing down the query. I haven't done experiments on this to see how big an effect it has though. Any other practical or aesthetic Pros and Cons people can suggest? -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
SELECT * is almost always bad style. It shouldnt be so hard to This is another religious issue you'll find people pretty adamant on both sides. Seems so. I tend to prefer to use SELECT * because it reduces repetition and improves modularity. There are fewer places in the code that need to know about a new column being added to a table (or expression to a query) and fewer places that need to know about a new column (or expression) being needed in the final result. Same here. Simplifies request generation, simplifies coding, less change-tracking... [I am assuming you use look up columns by name. To me it seems the only reasonable approach for all but the simplest cases] Of course. SELECT * and then using the columns by number is, well, like dropping a brick and hoping it doesnt land on your foot. However many databases and interfaces have some pretty strong practical problems that result from using it. So it's pretty standard DBA rule-of-thumb material to discourage its use. For instance the Python MySQL has no such functionality. PHP-Mysql has, though. The only problem I've run into with Postgres is that there's no way to *remove* a column from a list of columns without listing all the non-removed columns. True. In that case, the database library which generates queries, should offer this kind of functionality, and generate the SELECT col1, ..., colN appropriately (mine does). In fact I like SELECT * because I find generated queries to be, sometimes, kludgey at best to manipulate when using JOIN's or complicated WHEREs. Let me explain. In my DB library, I have classmethods to create a class instance from a row. For instance, Class.GetByKey( value ) looks up on the primary key. Which field is the primary key is declared once in the class definition. GetByKey is part of the base class, of course (some kind of DbRow). Some classes will have special methods, like fulltext search methods, most recent search methods... So, the SQL is neatly hidden in these methods, but sometimes you do need to mess with it : Then, I have something like Class.GetSQL( SELECT m.* FROM mytable m, othertable o WHERE some joins ... ) which just executes the SQL string (with parameters if needed), retrieves the rows as mappings of column_name=value and instanciates the objects. By the way, all the Get...() classmethods are usually one line, calling GetSQL(). And there's no way to disambiguate if you add a second column by the same name. MySQL has an easy solution : columns with the same name overwrite one another. Neat for joins on keys which have the same name, but still leaves this dropping a brick feeling. So you have some situations where you can't add an expression with the correct name without explicitly listing every other column. Well, that's what DB libraries are for, ain't they ? There may be performance implications for having more columns than necessary in a select list as well. If you just need the product name, don't retrieve the description ;) I added an additional, optional parameter to specify the columns to remove. In this case, the instance members are not created, and if you try to use them, an exception is raised... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] change natural column order
Using SELECT * FROM table_name from the PSQL prompt or any other
interactive tool is perfectly fine.
Putting SELECT * FROM table_name into a compiled program using libpq or
ESQL is a code defect. Period.
ALTER TABLE ADD COLUMN /* Most frequent defect maker for SELECT * */
ALTER TABLE DROP COLUMN /* If you didn't need the column, who cares */
ALTER TABLE RENAME COLUMN /* This will be a problem either way, but at
least you will find out about it. It also shows why renaming columns is almost
always a very, very bad idea after any release. */
ALTER TABLE SET WITHOUT OIDS {PG specific} /* One fewer column now, and
all the column numbers are now 'off-by-one' */
DROP TABLE/CREATE TABLE /* New version may have the same name and the
same number of columns, and they may even have the same data types but there is
no guarantee that the meaning is the same. */
The list goes on and on.
It is a defect of equal magnitude to assume that columns are returned in any
particular order unless specified in a column list (again, from a program and
not interactively).
Another typical defect is to assume that columns come backed ordered by the
primary key if the table is clustered on the primary key column. You can have
a page split with many database systems and so there is no guarantee that data
will be returned in order without an ORDER BY clause -- clustered or not.
Any of (ASSMUME NO COLUMN CHANGES/ASSUME COLUMN ORDER/ASSUME CLUSTERED KEY SORT
ORDER) would cause me to fail code in a code review.
IMO-YMMV
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] change natural column order
On Tue, Nov 30, 2004 at 03:03:37PM -0800, Dann Corbit wrote:
Using SELECT * FROM table_name from the PSQL prompt or any other
interactive tool is perfectly fine.
Putting SELECT * FROM table_name into a compiled program using libpq or
ESQL is a code defect. Period.
This looks like misinformation, a misunderstanding of the data
available from libpq or a misunderstanding of how any language more
sophisticated than C[1] tends to access data structures
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
Cheers,
Steve
[1] Not that there's anything wrong with the moral equivalent of
PQgetvalue(res, 0, PQfnumber(res, foo)) other than a slightly
clumsy syntax.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
From: Steve Atkins [EMAIL PROTECTED]
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
because it does not work ? (you mean $row-{'foo'})
sorry, could not resist :-)
gnari
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of gnari
Sent: Tuesday, November 30, 2004 4:48 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] change natural column order
From: Steve Atkins [EMAIL PROTECTED]
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
because it does not work ? (you mean $row-{'foo'})
sorry, could not resist :-)
There is an exception to every rule. If you need a hash for the whole
row, then you need all the columns.
And while we are at it:
$dbh-selectrow_hashref(SELECT * FROM $table LIMIT 1);
Or something along those lines would be a bit less absurd.
To do a table scan to select a single value is criminal.
SELECT * is a horrible disease, coughed up by those who do not know
what they want most of the time.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
From: Dann Corbit [EMAIL PROTECTED]
From: Steve Atkins [EMAIL PROTECTED]
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
There is an exception to every rule. If you need a hash for the whole
row, then you need all the columns.
I think the point was that when the language/libs allow for
easy reading of a row into a hash, it becomes the most
natural and least error prone way. If you add or rename a column,
there are fewer places in the code you need to change.
gnari
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
Dann Corbit [EMAIL PROTECTED] writes: Putting SELECT * FROM table_name into a compiled program using libpq or ESQL is a code defect. Period. ALTER TABLE ADD COLUMN /* Most frequent defect maker for SELECT * */ Whatever are you talking about? I've never tried ESQL precisely because it seems a terribly old-fashioned interface. Does it not support referring to columns by name? Even in libpq you can use PQfnumber or load all the columns found with PQfname into an associative array like higher level language drivers typically do automatically. DROP TABLE/CREATE TABLE /* New version may have the same name and the same number of columns, and they may even have the same data types but there is no guarantee that the meaning is the same. */ Um, well, there was no guarantee the meaning was the intended meaning in the first place except for your own interface documentation. Obviously if you replace the table with a new one you're either maintaining compatible semantics or else you're changing the interface and it will require some code changes. It is a defect of equal magnitude to assume that columns are returned in any particular order unless specified in a column list (again, from a program and not interactively). Actually the spec does guarantee that the columns have a fixed defined ordering. However I would agree it would be a poor design to depend on that ordering since it's not self-documenting and requires close synchronization between far distant pieces of code. But then I think it's bad to depend on ordering even when it is an explicitly listed column list because it requires close synchronization between two pieces of code even if they aren't too far distant. I prefer referring to columns by name in all but the simplest cases because it means the only synchronization is the presence or lack of a column, not the precise position in the output list. I'm free to add columns to a select list in the logical position without having to make adjustments elsewhere in the code. Another typical defect is to assume that columns come backed ordered by the primary key if the table is clustered on the primary key column. You can have a page split with many database systems and so there is no guarantee that data will be returned in order without an ORDER BY clause -- clustered or not. You're confusing columns with rows. Without an ORDER BY clause there's no guarantee of the order of the rows. Not because of page splits or any other specific technical reason. There just isn't. There are any number of reasons the database might choose another ordering to return rows. In the case of Postgres clustering doesn't really work anyways so *any* sequential table scan without an explicit ORDER BY will be out of order unless you haven't made any data changes since the clustering. Any of (ASSUME NO COLUMN CHANGES/ASSUME COLUMN ORDER/ASSUME CLUSTERED KEY SORT ORDER) would cause me to fail code in a code review. IMO-YMMV Well with Postgres and reasonably modern drivers none of the above impact at all on whether SELECT * is a good idea or not. Like I said in my post. Because of historical problems with various other databases the rule of thumb that SELECT * is bad has become entrenched in standard DBA dogma. One of those historical problems is that some interfaces depend on column ordering in result sets and have particular problems dealing when the columns change in any way. As you point out this can happen for lots of reasons, not just because SELECT * is used. Thankfully nowadays we get to use much more flexible interfaces that find columns based on column names that don't suffer from these problems. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] change natural column order
Regarding the Natural Order of columns. Why if we can delete a column from the middle of a table now, change the type of that column to something totally different, eg text - int. Can we not move the order of the rows around, and when the new row is written to disk in the new arrangement. Or more accurately, why is it not possible to add a new column, not at the end of the list. It's probably more complicated that I think, as that's usually the case. We don't need logical and physical mapping, probably just the ability to insert a column not on the end. Sorry if this comment is in the wrong place, I've been following the General and Hackers discussions and decided to post now after deleting the other posts. Regards Russell Smith. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
Hi, Am Mittwoch, den 01.12.2004, 16:46 +1100 schrieb Russell Smith: Regarding the Natural Order of columns. Why if we can delete a column from the middle of a table now, change the type of that column to something totally different, eg text - int. Can we not move the order of the rows around, and when the new row is written to disk in the new arrangement. Or more accurately, why is it not possible to add a new column, not at the end of the list. It's probably more complicated that I think, as that's usually the case. How do you select middle of a table ? All I know is how to refer to columns by name. And for that it is unimportant in which order they appear in SELECT * In fact its not recommendet to use SELECT * in production code. And again, SELECTS on one table only are very rare in most projects I've seen - so why pull any extra column you arent going to use in an app over the wire? There are edge cases when you want to write a generic database tool where you just display data as it is, but then you could easily maintain your own order of columns in a table. Usually a table even has some columns with keys, would you show that to a user? And if so, why? Some even dont use a single table here because they dont feel comfortable let the average user fiddle with the schema. So whats the point to call for that cosmetic feature again and again? Just my 2ct Tino ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
