headers
John Fabiani | 8 Jun 2012 01:17

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

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Permalink | Reply |
headers
David Johnston | 8 Jun 2012 01:32
Picon
Favicon

Re: using ordinal_position

> -----Original Message-----
> From: pgsql-sql-owner <at> postgresql.org [mailto:pgsql-sql-
> owner <at> postgresql.org] On Behalf Of John Fabiani
> Sent: Thursday, June 07, 2012 7:18 PM
> To: pgsql-sql <at> 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)
(Continue reading)

Permalink | Reply |
headers
Igor Neyman | 11 Jun 2012 15:53
Favicon

Re: using ordinal_position


> -----Original Message-----
> From: John Fabiani [mailto:johnf <at> jfcomputer.com]
> Sent: Thursday, June 07, 2012 7:18 PM
> To: pgsql-sql <at> 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".
(Continue reading)

Permalink | Reply |
headers
John Fabiani | 11 Jun 2012 17:10

Re: using ordinal_position

On 06/11/2012 06:53 AM, Igor Neyman wrote:
>
>> -----Original Message-----
>> From: John Fabiani [mailto:johnf <at> jfcomputer.com]
>> Sent: Thursday, June 07, 2012 7:18 PM
>> To: pgsql-sql <at> 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".
>
(Continue reading)

Permalink | Reply |
headers
Igor Neyman | 11 Jun 2012 17:14
Favicon

Re: using ordinal_position


> -----Original Message-----
> From: John Fabiani [mailto:johnf <at> jfcomputer.com]
> Sent: Monday, June 11, 2012 11:11 AM
> To: Igor Neyman
> Cc: pgsql-sql <at> postgresql.org
> Subject: Re: using ordinal_position
> 
> On 06/11/2012 06:53 AM, Igor Neyman wrote:
> >
> >> -----Original Message-----
> >> From: John Fabiani [mailto:johnf <at> jfcomputer.com]
> >> Sent: Thursday, June 07, 2012 7:18 PM
> >> To: pgsql-sql <at> 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.
> >>
(Continue reading)

Permalink | Reply |

Gmane