是否可以更改Postgres中列的自然顺序？ [英] Is it possible to change the natural order of columns in Postgres?

问题描述

是否可以在Postgres 8.1中更改列的自然顺序？

Is it possible to change the natural order of columns in Postgres 8.1?

我知道您不应该依赖列顺序-这不是 essential 到我正在做的事情-我只需要它使一些自动生成的东西以更令人愉悦的方式出现，以便字段顺序从pgadmin一直到后端一直匹配到

I know that you shouldn't rely on column order - it's not essential to what I am doing - I only need it to make some auto-generated stuff come out in a way that is more pleasing, so that the field order matches all the way from pgadmin through the back end and out to the front end.

推荐答案

您实际上可以直接更改列顺序，但是我几乎不建议这样做，您应该

You can actually just straight up change the column order, but I'd hardly recommend it, and you should be very careful if you decide to do it.

例如。

# CREATE TABLE test (a int, b int, c int);
# INSERT INTO test VALUES (1,2,3);
# SELECT * FROM test;
 a | b | c 
---+---+---
 1 | 2 | 3
(1 row)

现在请注意，您需要使用postgres用户连接到数据库，可以修改系统表。

Now for the tricky bit, you need to connect to your database using the postgres user so you can modify the system tables.

# SELECT relname, relfilenode FROM pg_class WHERE relname='test';
 relname | relfilenode 
---------+-------------
 test_t  |       27666
(1 row)

# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid=27666;
 attrelid | attname  | attnum 
----------+----------+--------
    27666 | tableoid |     -7
    27666 | cmax     |     -6
    27666 | xmax     |     -5
    27666 | cmin     |     -4
    27666 | xmin     |     -3
    27666 | ctid     |     -1
    27666 | b        |      1
    27666 | a        |      2
    27666 | c        |      3
(9 rows)

attnum是唯一列，因此在修改时需要使用一个临时值这样的列号：

attnum is a unique column, so you need to use a temporary value when you're modifying the column numbers as such:

# UPDATE pg_attribute SET attnum=4 WHERE attname='a' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=1 WHERE attname='b' AND attrelid=27666;
UPDATE 1
# UPDATE pg_attribute SET attnum=2 WHERE attname='a' AND attrelid=27666;
UPDATE 1

# SELECT * FROM test;
 b | a | c 
---+---+---
 1 | 2 | 3
(1 row)

再次，因为这与数据库系统表有关，如果您觉得自己非常小心

Again, because this is playing around with database system tables, use extreme caution if you feel you really need to do this.

此功能自postgres 8.3起使用，对于以前的版本，您的里程可能会有所不同。

This is working as of postgres 8.3, with prior versions, your milage may vary.

这篇关于是否可以更改Postgres中列的自然顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！