Re: Changing Field Ordinal Position

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Andreas Kretschmer
Sent: Friday, October 22, 2010 10:49 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Changing Field Ordinal Position

Chris Campbell <ccampbell@cascadeds.com> wrote:

> Using PostgreSQL 9.0.1
> 
>  
> 
> Hi, is it possible to change the ordinal position of fields in a table, or,
> more specifically, be able to specify the ordinal position when adding new
> fields to a table.  I see that each field has a property called “position” yet

>>No. There is no way to do that, sorry.

>>Consider an other way: specify all columns in the right order in your
>>select-statement. Or create a view with the (for you) right order.


Hey Andreas, thanks for your reply

Yeah for us it's just a consistency and readability thing when working within pgAdminIII.  Our views specify the order
wewant to see in the interface so no worries there.  We are upgrading a project that uses MS-Access to use Postgres.
Whenwe create tables the standard is primekey field is always first, then any foreign keys fields, non foreign key
fields,and at the end, record maintenance fields like created on/by and modified on/by.  The routine we have reads the
tablein Access, then based on the Access structure, creates the corresponding data structure in Postgres, exports the
datato text files, which are then read into the newly created tables in Postgres.  And because this upgrade entails
changesto certain tables, we add those new fields and modify or drop other fields once the data has been imported.
Thisall occurs as a one click process.  I was just hoping there was something simple to make this happen rather than
employingpredefined temporary tables with the table field order we want.
 

It's clearly a preference thing for us. After 10 years of having and enforcing table field order standards, it's kind
oflike sitting down at a restaurant and seeing your fork and spoon on the same side of the plate and your knife over by
thesalt and pepper shakers.  It doesn't affect the meal, just looks weird (to us).  
 

Thanks again

- CBC