Thread: Changing Field Ordinal Position
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 there seems to be no way to change it, or, when fields are dropped from a table does it seem to update.
I looked at the ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] ) and RESET ( attribute_option [, ... ] ) but according to the documentation this only applies to n_distinct and n_distinct_inherited properties. Thank you for any assistance…
Chris Campbell
Cascade Data Solutions, Inc.
ccampbell@CascadeDS.com
(800) 280-2090
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. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
-----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