Обсуждение: using ordinal_position
I'm attempting to retrieve data using a select statement without knowing the column names. I know the ordinal position but not the name of the column (happens to be a date::text and I have 13 fields). Below provides the name of the column in position 3: select column_name from (select column_name::text, ordinal_position from information_schema.columns where table_name='wk_test') as foo where ordinal_position = 3; But how can I use the above as a column name in a normal select statement. Unlike other databases I just can't use ordinal position in the select statement - RIGHT??? Johnf
> -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of John Fabiani > Sent: Thursday, June 07, 2012 7:18 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] using ordinal_position > > I'm attempting to retrieve data using a select statement without knowing the > column names. I know the ordinal position but not the name of the column > (happens to be a date::text and I have 13 fields). > > Below provides the name of the column in position 3: > > select column_name from (select column_name::text, ordinal_position from > information_schema.columns where > table_name='wk_test') as foo where ordinal_position = 3; > > But how can I use the above as a column name in a normal select statement. > > Unlike other databases I just can't use ordinal position in the select > statement - RIGHT??? > > Johnf > This seems like a seriously messed up requirement but I guess the easiest way would be as follows: SELECT tbl.col3 FROM (SELECT * FROM table) tbl (col1, col2, col3) Basically you select ALL columns (thus not caring about their names and always getting the defined order) from the table and give explicit aliases to columns 1 though N where N is the desired column position you want to return. All subsequent columns will retain their original names. If the parent query you can then simply select the column alias you assigned to the desired column position. If you query the catalog for the true column name you would have to use pl/pgSQL and EXECUTE to run the query against a manually built (stringified) query; SQL proper does not allow for table and column names to be variable. That said you may find it worthwhile to publish the WHY behind your inquiry to see if some other less cumbersome and error-prone solution can be found. While column order is fairly static it is not absolute and if the column order were to change you would have no way of knowing. At least when using actual column names the query would fail with an unknown column name exception instead of possibly silently returning bad data. The extra layer of indirection just seems dirty to me - but aside from the possibility of column order changes I don't see any major downsides. Since you are just dealing with column aliases it should not meaningfully impact query plan generation and thus it should be no slower than a more direct query. David J.
> -----Original Message----- > From: John Fabiani [mailto:johnf@jfcomputer.com] > Sent: Thursday, June 07, 2012 7:18 PM > To: pgsql-sql@postgresql.org > Subject: using ordinal_position > > I'm attempting to retrieve data using a select statement without > knowing the column names. I know the ordinal position but not the name > of the column (happens to be a date::text and I have 13 fields). > > Below provides the name of the column in position 3: > > select column_name from (select column_name::text, ordinal_position > from information_schema.columns where > table_name='wk_test') as foo where ordinal_position = 3; > > But how can I use the above as a column name in a normal select > statement. > > Unlike other databases I just can't use ordinal position in the select > statement - RIGHT??? > > Johnf David gave you already pretty complete answer. I just wonder what are these "other" RDBMSs that allow to use ordinal column positions in a query? I am familiar with a few (in addition to Postgress), and none of them does that, not in "select" list., though everybodyallow ordinal position from "select" list in "order by" and "group by". Regards, Igor Neyman
On 06/11/2012 06:53 AM, Igor Neyman wrote: > >> -----Original Message----- >> From: John Fabiani [mailto:johnf@jfcomputer.com] >> Sent: Thursday, June 07, 2012 7:18 PM >> To: pgsql-sql@postgresql.org >> Subject: using ordinal_position >> >> I'm attempting to retrieve data using a select statement without >> knowing the column names. I know the ordinal position but not the name >> of the column (happens to be a date::text and I have 13 fields). >> >> Below provides the name of the column in position 3: >> >> select column_name from (select column_name::text, ordinal_position >> from information_schema.columns where >> table_name='wk_test') as foo where ordinal_position = 3; >> >> But how can I use the above as a column name in a normal select >> statement. >> >> Unlike other databases I just can't use ordinal position in the select >> statement - RIGHT??? >> >> Johnf > David gave you already pretty complete answer. > I just wonder what are these "other" RDBMSs that allow to use ordinal column positions in a query? > I am familiar with a few (in addition to Postgress), and none of them does that, not in "select" list., though everybodyallow ordinal position from "select" list in "order by" and "group by". > > Regards, > Igor Neyman > > > VFP uses position (you might not consider DBF a database). MsSQl (ordinal_position). Those are the only two I'm aware of. Johnf
> -----Original Message----- > From: John Fabiani [mailto:johnf@jfcomputer.com] > Sent: Monday, June 11, 2012 11:11 AM > To: Igor Neyman > Cc: pgsql-sql@postgresql.org > Subject: Re: using ordinal_position > > On 06/11/2012 06:53 AM, Igor Neyman wrote: > > > >> -----Original Message----- > >> From: John Fabiani [mailto:johnf@jfcomputer.com] > >> Sent: Thursday, June 07, 2012 7:18 PM > >> To: pgsql-sql@postgresql.org > >> Subject: using ordinal_position > >> > >> I'm attempting to retrieve data using a select statement without > >> knowing the column names. I know the ordinal position but not the > >> name of the column (happens to be a date::text and I have 13 > fields). > >> > >> Below provides the name of the column in position 3: > >> > >> select column_name from (select column_name::text, ordinal_position > >> from information_schema.columns where > >> table_name='wk_test') as foo where ordinal_position = 3; > >> > >> But how can I use the above as a column name in a normal select > >> statement. > >> > >> Unlike other databases I just can't use ordinal position in the > >> select statement - RIGHT??? > >> > >> Johnf > > David gave you already pretty complete answer. > > I just wonder what are these "other" RDBMSs that allow to use ordinal > column positions in a query? > > I am familiar with a few (in addition to Postgress), and none of them > does that, not in "select" list., though everybody allow ordinal > position from "select" list in "order by" and "group by". > > > > Regards, > > Igor Neyman > > > > > > > VFP uses position (you might not consider DBF a database). MsSQl > (ordinal_position). Those are the only two I'm aware of. > > Johnf Did you mean MySQL or MS SQL? Because MS SQL Server does not allow ordinal position AFAIK. Igor