Re: Changing Field Ordinal Position

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

Chris Campbell <ccampbell(at)cascadeds(dot)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 we want to see in the interface so no worries there. We are upgrading a project that uses MS-Access to use Postgres. When we 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 table in Access, then based on the Access structure, creates the corresponding data structure in Postgres, exports the data to text files, which are then read into the newly created tables in Postgres. And because this upgrade entails changes to certain tables, we add those new fields and modify or drop other fields once the data has been imported. This all occurs as a one click process. I was just hoping there was something simple to make this happen rather than employing predefined 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 of like sitting down at a restaurant and seeing your fork and spoon on the same side of the plate and your knife over by the salt and pepper shakers. It doesn't affect the meal, just looks weird (to us).

Thanks again

- CBC