Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F31659FBE5B for ; Wed, 6 Jun 2007 11:22:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 66808-06 for ; Wed, 6 Jun 2007 11:22:20 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 691259FBE80 for ; Wed, 6 Jun 2007 11:22:29 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.8/8.13.8) with ESMTP id l56EMKUu067878 for ; Wed, 6 Jun 2007 11:22:20 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.8/8.13.8/Submit) id l56Dxf2R060927 for pgsql-admin@postgresql.org; Wed, 6 Jun 2007 10:59:41 -0300 (ADT) (envelope-from news) From: Erwin Brandstetter X-Newsgroups: pgsql.admin Subject: Change order of table-columns in pg_catalog.pg_attribute.attnum Date: Wed, 06 Jun 2007 06:59:48 -0700 Organization: http://groups.google.com Lines: 59 Message-ID: <1181138388.508384.51110@q75g2000hsh.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: q75g2000hsh.googlegroups.com; posting-host=62.116.9.170; posting-account=motVOw0AAAAl5BtLWL3QN4EcB1CKG8U3 To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/38 X-Sequence-Number: 25706 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