0

What I want to do: Select all columns but apply a function to one of the selected columns in a shorthand manner such that I don't have to type out all of the column names

Current approach: I am currently using select table.* to select all columns in a table.

Example table

Name data1 data2 data3 ....
a    1     1     1
b    1     1     1
c    1     1     1

Code:

SELECT * from table

Desired outcome: I want to be able to select all columns then apply a function to a specific column, is it possible to do this with select * or does it require selecting all column names and applying a function to my specified column?

1

Select all columns ... such that I don't have to type out all of the column names

But that is exactly what you should be doing!

Never use select * in production code.

Why? Two reasons.

  1. The database has to work harder, first to work out which columns exist, construct the query and then actually retrieve the data for you. OK, the amount of extra work is small but involves core, data dictionary tables which can result in performance bottlenecks.

  2. Databases are inherently shared environments. Today, your query might be perfectly happy, pulling back all three of the columns in a given table. Life is Good. Tomorrow, someone else might add a hundred BLOB fields into that table and suddenly your query's performance goes through the floor, pulling back all that data in which your application has absolutely no interest.

Always specify the columns that your query requires explicitly.

Also, are those really your column names? If so, then your table design worries me greatly.

Database != Spreadsheet. 

Basic Data Normalisation rules should extract those repeated attributes out into a another [sub-]table, rather than running them "along" a row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.