Thread: SELECT * and column ordering
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
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.
On Wed, Jan 16, 2013 at 11:45 AM, Rob Sargent <robjsargent@gmail.com> wrote: > What environment are you in. In jdbc you can address the resultset by > column name. C++, so it's quite inefficient to always use names. Chris Angelico
On 01/15/2013 07:08 PM, Meta Seller Dev/Admin wrote: > On Wed, Jan 16, 2013 at 11:45 AM, Rob Sargent <robjsargent@gmail.com> wrote: >> What environment are you in. In jdbc you can address the resultset by >> column name. > > C++, so it's quite inefficient to always use names. > > Chris Angelico > > Craft you're own metadata/lookup for each of the tables? Run against well-ordered views?
On 2013-01-16, Meta Seller Dev/Admin <metaseller@gmail.com> 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. walking the list of column names you get back from "select *" is likely to be faster than any of the other options, and you only need to do it once and (and then store the indices of the interesting columns in an array for later reference.) PQgetvalue() has O(1) complexity so once you know the column number you want the cost is the same. -- ⚂⚃ 100% natural