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