From: Michael Glaesemann <grzm@myrealbox.com>
To: daryl@brandywine.com
Cc: fmiddleton@verizon.net
Cc: pgsql-sql@postgresql.org
Subject: Re: changing a column's position in table, how do you do that
Date: Thu, 29 Sep 2005 06:28:01 +0900
Message-ID: <211B0819-5AB1-4F45-BC8D-753457956688@myrealbox.com> (raw)
In-Reply-To: <433AF11D.801@brandywine.com>
References: <4339CD1B.10404@verizon.net>
<433AF11D.801@brandywine.com>
On Sep 29, 2005, at 4:38 , Daryl Richter wrote:
> Ferindo Middleton Jr wrote:
>
>> Is there a way to change the position attribute of a column in a
>> table? I have data that I need to import into various tables in my
>> db on a consistent basis... I usually us e the COPY ... FROM query
>> but I can't control the -order- of the fields my client dumps the
>> data so I would like to be able to change the position the columns
>> in my table to be able to better align the data to be imported
>> with the format of my table. I was thinking I could do something
>> like "ALTER TABLE ALTER COLUMN ... " or something like that to
>> change the columns position in the table but I can't figure out how.
>> Ferindo
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>
> Constantly changing the column order of a table doesn't make sense
> (and is a pain!).
>
> I would suggest setting up a temporary staging table with generic
> column names to take in the input data and then using SQL to move
> it into its permanent location.
Or specify the columns directly in the copy statement. Instead of
copy foo from <file>, do copy foo (bar,baz,bat) from <file>. Then you
only have to change the copy statement.
While SQL does allow columns to be referenced by column order, it's
far, far better to consider the columns of a table to be unordered
and (nearly) always specify your columns explicitly. It gives you
much more flexibility. Changing a few SQL statements to return (or
insert) the columns how you want them is much easier than changing
the underlying table structure and having to make sure *all* of your
queries then fit the new table structure.
Michael Glaesemann
grzm myrealbox com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@myrealbox.com, daryl@brandywine.com, fmiddleton@verizon.net
Subject: Re: changing a column's position in table, how do you do that
In-Reply-To: <211B0819-5AB1-4F45-BC8D-753457956688@myrealbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox