Thread: select column by position

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Is there a way to select a column in a table by its position rather than the field name? If I
understandcorrectly column position in a table is fixed. I’ve not been able to find anything in the archives or docs to
indicatethat it’s possible to select by position.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">For example:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE Accessions (</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">    </span>accession_id INT8 PRIMARY KEY DEFAULT
nextval('accession_id_seq'),</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">    </span>accenumb VARCHAR(255) NOT NULL,</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">    </span>instcode_id INT4 NOT NULL CONSTRAINT
Accessions_instcode_id_fkREFERENCES Institutions(institution_id),</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">    </span>accename VARCHAR(255)</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">);</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I would like to select accession_id by indicating column 1 somehow. Is this at all
possible?</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Cheers,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Jennifer</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></div><p align="left"><font color="#0000ff"><font color="#000000" face="Tahoma"
size="2">*****************************************************************************************</font></font><p
align="left"><fontcolor="#0000ff"><font color="#000000" face="Tahoma"
size="2"><strong>DISCLAIMER:</strong></font></font><palign="left"><font color="#0000ff"><font color="#000000"
face="Tahoma"size="2">This email is from the Scottish Crop Research Institute, but the views expressed by the sender
arenot necessarily the views of SCRI and its subsidiaries.  This email and any files transmitted with it are
confidentialto the intended recipient at the e-mail address to which it has been addressed.  It may not be disclosed or
usedby any other than that addressee.<br />If you are not the intended recipient you are requested to preserve this
confidentialityand you must not use, disclose, copy, print or rely on this e-mail in any way. Please notify <a
href="mailto:mail@scri.sari.ac.uk">mail@scri.sari.ac.uk</a>quoting the name of the sender and delete the email from
yoursystem.</font></font><p align="left"><font color="#0000ff"><font color="#000000" face="Tahoma" size="2">Although
SCRIhas taken reasonable precautions to ensure no viruses are present in this email, neither the Institute nor the
senderaccepts any responsibility for any viruses, and it is your responsibility to scan the email and the attachments
(ifany).<br /></font></font> 

On Fri, 24 Sep 2004, Jennifer Lee wrote:
> Is there a way to select a column in a table by its position rather than
> the field name? If I understand correctly column position in a table is
> fixed. I've not been able to find anything in the archives or docs to
> indicate that it's possible to select by position.

The general response will be that you don't really want to do this.
You should figure out some other way to query the database that doesn't
rely on column position. For example, dropping and adding columns
with ALTER TABLE command could change their positions.

If you still want to go about it, I suppose it might be possible to
write a PL/pgSQL stored procedure that determines the column name
from the system catalogs, then uses EXECUTE to run the query with
the real column name substituted in the query. See Executing
Dynamic Commands in the documentation:

http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Documentation about what is stored in the system catalogs can be found:
http://www.postgresql.org/docs/7.4/interactive/catalogs.html