Thread: change natural column order

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?

Thanks!

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

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!

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

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

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!

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 <joolz@arbodienst-limburg.nl>:
> 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

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

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 <joolz@arbodienst-limburg.nl>:
>> 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.

> 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,
s.specials_new_products_price, p.products_price) as final_price from " .
TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on
p.products_id = s.products_id where p.products_status = '1' and
pd.products_id = p.products_id and pd.language_id = '" .
(int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
       }
     } else {
// show the products in a given categorie
       if (isset($HTTP_GET_VARS['filter_id']) &&
tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
         $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['filter_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)$current_category_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, NU



</code>

Richard Huxton <dev@archonet.com> 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

Tom Lane wrote:

>Richard Huxton <dev@archonet.com> 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

Pierre-Frédéric Caillaud <lists@boutiquenumerique.com> 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

>> > 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...

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
showswhy 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
havethe 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
columnlist (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
theprimary key column.  You can have a page split with many database systems and so there is no guarantee that data
willbe 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
codereview. 

IMO-YMMV

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.

From: "Steve Atkins" <steve@blighty.com>
>
> 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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of gnari
Sent: Tuesday, November 30, 2004 4:48 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] change natural column order

From: "Steve Atkins" <steve@blighty.com>
>
> 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.
<<

From: "Dann Corbit" <DCorbit@connx.com>

> From: "Steve Atkins" <steve@blighty.com>
> >
 > > 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

"Dann Corbit" <DCorbit@connx.com> 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

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,
asthat'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
postnow after 
deleting the other posts.

Regards

Russell Smith.

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
moreaccurately, 
> 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,
asthat'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