pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedchanging a column's position in table, how do you do that
5+ messages / 5 participants
[nested] [flat]
* changing a column's position in table, how do you do that
@ 2005-09-27 22:52 Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-28 03:24 ` Re: changing a column's position in table, how do you do that Chris Browne <cbbrowne@acm.org>
2005-09-28 19:38 ` Re: changing a column's position in table, how do you do that Daryl Richter <daryl@brandywine.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ferindo Middleton Jr @ 2005-09-27 22:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing a column's position in table, how do you do that
2005-09-27 22:52 changing a column's position in table, how do you do that Ferindo Middleton Jr <fmiddleton@verizon.net>
@ 2005-09-28 03:24 ` Chris Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 5+ messages in thread
From: Chris Browne @ 2005-09-28 03:24 UTC (permalink / raw)
To: pgsql-sql
fmiddleton@verizon.net (Ferindo Middleton Jr) writes:
> 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.
If you're running PostgreSQL 7.4 or later, you can specify field names
in the COPY statement. That strikes me as a better way to control
this.
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Rules of the Evil Overlord #65. "If I must have computer systems with
publically available terminals, the maps they display of my complex
will have a room clearly marked as the Main Control Room. That room
will be the Execution Chamber. The actual main control room will be
marked as Sewage Overflow Containment." <http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing a column's position in table, how do you do that
2005-09-27 22:52 changing a column's position in table, how do you do that Ferindo Middleton Jr <fmiddleton@verizon.net>
@ 2005-09-28 19:38 ` Daryl Richter <daryl@brandywine.com>
2005-09-28 21:28 ` Re: changing a column's position in table, how do you do that Michael Glaesemann <grzm@myrealbox.com>
1 sibling, 1 reply; 5+ messages in thread
From: Daryl Richter @ 2005-09-28 19:38 UTC (permalink / raw)
To: fmiddleton@verizon.net; +Cc: pgsql-sql
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.
--
Daryl
"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing a column's position in table, how do you do that
2005-09-27 22:52 changing a column's position in table, how do you do that Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-28 19:38 ` Re: changing a column's position in table, how do you do that Daryl Richter <daryl@brandywine.com>
@ 2005-09-28 21:28 ` Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2005-09-28 21:28 UTC (permalink / raw)
To: daryl@brandywine.com; +Cc: fmiddleton@verizon.net; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: changing a column's position in table, how do you do that
@ 2005-09-28 00:25 Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stewart Ben (RBAU/EQS4) * @ 2005-09-28 00:25 UTC (permalink / raw)
To: pgsql-sql
Ferindo,
> Is there a way to change the position attribute of a column
> in a table?
AFAIK, there's no way to change this easily. The best way to do it would
be as follows:
BEGIN WORK;
LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE;
ALTER TABLE mytable ADD COLUMN col_to_move_2 coltype;
UPDATE mytable SET col_to_move_2 = col_to_move;
ALTER TABLE mytable DROP COLUMN col_to_move;
ALTER TABLE mytable RENAME col_to_move_2 TO col_to_move;
COMMIT WORK;
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-28 21:28 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-27 22:52 changing a column's position in table, how do you do that Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-28 03:24 ` Chris Browne <cbbrowne@acm.org>
2005-09-28 19:38 ` Daryl Richter <daryl@brandywine.com>
2005-09-28 21:28 ` Michael Glaesemann <grzm@myrealbox.com>
2005-09-28 00:25 Re: changing a column's position in table, how do you do that Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
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