pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Change order of table-columns in pg_catalog.pg_attribute.attnum
5+ messages / 4 participants
[nested] [flat]

* Change order of table-columns in pg_catalog.pg_attribute.attnum
@ 2007-06-06 13:59 Erwin Brandstetter <brsaweda@gmail.com>
  2007-06-06 14:38 ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Alvaro Herrera <alvherre@commandprompt.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Erwin Brandstetter @ 2007-06-06 13:59 UTC (permalink / raw)
  To: pgsql-admin

Hi group!

If I want to change the default order of two columns of a table, can I
just manipulate the values in
pg_catalog.pg_attribute.attnum?
I am trying to do this in pg 8.1.9. Works the same in pg 8.2.x I would
assume?

BEGIN;
UPDATE pg_catalog.pg_attribute SET attnum = 4
WHERE attrelid = 12345
AND attname = 'col3'
AND attnum = 2;

UPDATE pg_catalog.pg_attribute SET attnum = 2
WHERE attrelid = 12345
AND attname = 'col2'
AND attnum = 3;

UPDATE pg_catalog.pg_attribute SET attnum = 3
WHERE attrelid = 12345
AND attname = 'col3'
AND attnum = 4;
COMMIT;

-- That's how I got the necessary data (attrelid, attnum):
SELECT a.attrelid, a.attname, a.attnum
FROM pg_class c, pg_namespace nc, pg_attribute a
WHERE c.relnamespace = nc.oid
AND a.attrelid = c.oid
AND nc.nspname = 'myschema'
AND c.relname = 'mytbl'
AND a.attnum >= 1;

- I assume I have to avoid holes in the numbering of the visible
attributes of the relation.
- To avoid temporary duplicates in attnum I first move one of the
columns to a new postition, so I need 3 operations to switch the
position of two fields.
- All in one transaction, so it should should be immune to other
people trying to access the table.

Anything else I need to bear in mind? Does it work like this at all?
Are there side effects?
I did not find any contradicting info here:
http://www.postgresql.org/docs/8.1/interactive/catalog-pg-attribute.html
I have tried it on a dummy table and it _seems_ to work ..

I know it is dangerous to mess with data in pg_catalog. But recreating
a table is such a pain when several foreign key constraints point to
it.

So maybe one of more knowing could comment on it?


Thanks in advance!
Regards
Erwin




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Change order of table-columns in pg_catalog.pg_attribute.attnum
  2007-06-06 13:59 Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
@ 2007-06-06 14:38 ` Alvaro Herrera <alvherre@commandprompt.com>
  2007-06-06 14:59   ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Alvaro Herrera @ 2007-06-06 14:38 UTC (permalink / raw)
  To: Erwin Brandstetter <brsaweda@gmail.com>; +Cc: pgsql-admin

Erwin Brandstetter escribió:
> Hi group!
> 
> If I want to change the default order of two columns of a table, can I
> just manipulate the values in
> pg_catalog.pg_attribute.attnum?

It works -- as long as the table is empty.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Change order of table-columns in pg_catalog.pg_attribute.attnum
  2007-06-06 13:59 Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
  2007-06-06 14:38 ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Alvaro Herrera <alvherre@commandprompt.com>
@ 2007-06-06 14:59   ` Tom Lane <tgl@sss.pgh.pa.us>
  2007-06-06 15:21     ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2007-06-06 14:59 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Erwin Brandstetter <brsaweda@gmail.com>; pgsql-admin

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Erwin Brandstetter escribió:
>> If I want to change the default order of two columns of a table, can I
>> just manipulate the values in
>> pg_catalog.pg_attribute.attnum?

> It works -- as long as the table is empty.

And as long as you have no views, foreign keys, indexes, defaults,
rules, etc etc etc referencing the columns.

Short answer is don't even think of trying it.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Change order of table-columns in pg_catalog.pg_attribute.attnum
  2007-06-06 13:59 Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
  2007-06-06 14:38 ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Alvaro Herrera <alvherre@commandprompt.com>
  2007-06-06 14:59   ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-06 15:21     ` Erwin Brandstetter <brsaweda@gmail.com>
  2007-06-06 15:50       ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Igor Neyman <ineyman@perceptron.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Erwin Brandstetter @ 2007-06-06 15:21 UTC (permalink / raw)
  To: pgsql-admin

On Jun 6, 4:59 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Alvaro Herrera <alvhe...@commandprompt.com> writes:
> > Erwin Brandstetter escribió:
> >> If I want to change the default order of two columns of a table, can I
> >> just manipulate the values in
> >> pg_catalog.pg_attribute.attnum?
> > It works -- as long as the table is empty.
>
> And as long as you have no views, foreign keys, indexes, defaults,
> rules, etc etc etc referencing the columns.
>
> Short answer is don't even think of trying it.

Thanks for your answers.
I had tried it with data in the table and it seemed to work, but it
does mess up views referencing the table. So, no go.

The only way to change the default order of columns is still to drop
the table and all references to it and recreate it all?


Regards
Erwin




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Change order of table-columns in pg_catalog.pg_attribute.attnum
  2007-06-06 13:59 Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
  2007-06-06 14:38 ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Alvaro Herrera <alvherre@commandprompt.com>
  2007-06-06 14:59   ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Tom Lane <tgl@sss.pgh.pa.us>
  2007-06-06 15:21     ` Re: Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
@ 2007-06-06 15:50       ` Igor Neyman <ineyman@perceptron.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Igor Neyman @ 2007-06-06 15:50 UTC (permalink / raw)
  To: Erwin Brandstetter <brsaweda@gmail.com>; pgsql-admin

More important question would be, why would you want to do this (change columns order)?
I can't think of any valid reason for this.

Igor Neyman 

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Erwin Brandstetter
Sent: Wednesday, June 06, 2007 11:22 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Change order of table-columns in pg_catalog.pg_attribute.attnum

On Jun 6, 4:59 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Alvaro Herrera <alvhe...@commandprompt.com> writes:
> > Erwin Brandstetter escribió:
> >> If I want to change the default order of two columns of a table, 
> >> can I just manipulate the values in pg_catalog.pg_attribute.attnum?
> > It works -- as long as the table is empty.
>
> And as long as you have no views, foreign keys, indexes, defaults, 
> rules, etc etc etc referencing the columns.
>
> Short answer is don't even think of trying it.

Thanks for your answers.
I had tried it with data in the table and it seemed to work, but it does mess up views referencing the table. So, no go.

The only way to change the default order of columns is still to drop the table and all references to it and recreate it all?


Regards
Erwin


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2007-06-06 15:50 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-06 13:59 Change order of table-columns in pg_catalog.pg_attribute.attnum Erwin Brandstetter <brsaweda@gmail.com>
2007-06-06 14:38 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-06 14:59   ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-06 15:21     ` Erwin Brandstetter <brsaweda@gmail.com>
2007-06-06 15:50       ` Igor Neyman <ineyman@perceptron.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