Re: change natural column order

Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)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