pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Erwin Brandstetter <brsaweda@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Change order of table-columns in pg_catalog.pg_attribute.attnum
Date: Wed, 06 Jun 2007 06:59:48 -0700
Message-ID: <1181138388.508384.51110@q75g2000hsh.googlegroups.com> (raw)

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




view thread (5+ messages)  latest in thread

Message-ID: <1181138388.508384.51110@q75g2000hsh.googlegroups.com>
Permalink:  ../1181138388.508384.51110@q75g2000hsh.googlegroups.com/
Also on:    postgresql.org/message-id/1181138388.508384.51110@q75g2000hsh.googlegroups.com

 · 

reply

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-admin@postgresql.org
  Cc: brsaweda@gmail.com
  Subject: Re: Change order of table-columns in pg_catalog.pg_attribute.attnum
  In-Reply-To: <1181138388.508384.51110@q75g2000hsh.googlegroups.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