> SELECT * is almost always bad style. It shouldnt be so hard toThis 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
