Hi! (I'm Chris Angelico posting from a work account - usually I'm here under the name rosuav@xxxxxxxxx.) 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
