Re: Select all fields except one

On Nov 28, 2007 12:18 PM, Vivek Khera <khera(at)kcilink(dot)com> wrote:
> For best practices, you should never use select * in your queries. You will
> inevitably end up with code that cannot deal with a schema change, and for
> any live system, you will have a schema change at some point...
>
> It is best to explicitly list the field names your code is expecting.
> Besides, I don't think you can do what you want to do with just SQL.

sometimes this is desirable. for example, you may have a view defined
as 'select * from foo' which you want to change with foo...and it is
not too difficult to write application code that is tolerant of adding
extra fields. also it is often useful to expand row and type
variables with '*' although this is kind of a postgresql peculiarity.

also consider 'insert...select' or 'create table as select' statements
that copy data from copy of table to another that by definition take
all the fields...so wouldn't a * be preferable to adjusting the field
list when it changes?

you are generally right though...i'm just laying out some excpetions.

merlin