Re: SELECT * and column ordering

On 01/15/2013 05:22 PM, Meta Seller Dev/Admin wrote:
> Hi! (I'm Chris Angelico posting from a work account - usually I'm here
> under the name rosuav@gmail.com.)
>
> I've run into a bit of an awkwardness in PostgreSQL setup and am
> hoping for some expert advice.
>
> Several of the tables I work with have two groups of fields: standard
> fields and "free fields". The standard fields have fixed names and
> will always exist; the free fields could be anything. In many cases, I
> want to enumerate all fields, including free ones, and for that I use
> "SELECT * FROM tablename".
>
> Normally, the standard fields are at the beginning of the table,
> having been created first. This is very convenient, as it lets me
> iterate over them first, and then pick up the free fields after. (Or
> alternatively, pick up a specific standard field by its index.) New
> free fields can be created at any time, and the program will happily
> pick them up and work with them. Order among free fields never
> matters.
>
> The problem comes when I want to add a new standard field. PostgreSQL
> currently doesn't have any way for me to insert a field into the
> beginning of a table, so I can't put it where it would have been if it
> had existed already. The table could be quite large, with several
> hundred free fields, and could have any number of rows.
>
> There are a number of options open to me. As per
> http://wiki.postgresql.org/wiki/Alter_column_position I could create a
> duplicate table, or duplicate columns. Both would involve a lot of
> disk churning, but that's my fallback if nothing else works.
>
> Ideally, what I'd like to do is become independent of the physical
> column order. If I were looking for just the standard fields, I could
> explicitly enumerate them in the SELECT statement, which would solve
> the problem. But doing this with an unknown set of fields requires the
> code either:
> * List fields via a catalogue table, join them into a SELECT list, and
> process that; or
> * Enumerate the system fields and then use a star, eg "SELECT
> _foo,_bar,_quux,* FROM tablename"
>
> The former requires two round-trips to the database instead of one.
> Since the application and database are on different computers, this
> could seriously impact performance, especially as this is a very
> common operation. The latter will give duplicates of the system
> fields, as the * expands to include them.
>
> Is there any way to do something like "SELECT x,y,z,ALL-THE-REST" that
> doesn't include the columns already named? If not, which of the
> options above, or what alternative, would you recommend?
>
> All advice gratefully appreciated!
>
> Chris Angelico
>
>
What environment are you in.  In jdbc you can address the resultset by
column name.