postgresql 是否可以更改 Postgres 中列的自然顺序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/126430/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to change the natural order of columns in Postgres?
提问by rjmunro
Is it possible to change the natural order of columns in Postgres 8.1?
是否可以更改 Postgres 8.1 中列的自然顺序？
I know that you shouldn't rely on column order - it's not essentialto 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.
我知道你不应该依赖列顺序——这对我正在做的事情来说不是必不可少的——我只需要它让一些自动生成的东西以更令人愉快的方式出现，以便字段顺序匹配所有从 pgadmin 通过后端并输出到前端的方式。
采纳答案by Russell
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.
您实际上可以直接更改列顺序，但我几乎不推荐它，如果您决定这样做，您应该非常小心。
eg.
例如。
# 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)
Now for the tricky bit, you need to connect to your database using the postgres user so you can modify the system tables.
现在是棘手的一点，您需要使用 postgres 用户连接到您的数据库，以便您可以修改系统表。
# 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 is a unique column, so you need to use a temporary value when you're modifying the column numbers as such:
attnum 是一个唯一的列，因此在修改列号时需要使用临时值：
# 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.
同样，因为这是在处理数据库系统表，所以如果您觉得确实需要这样做，请格外小心。
This is working as of postgres 8.3, with prior versions, your milage may vary.
这是从 postgres 8.3 开始工作的，对于以前的版本，您的里程可能会有所不同。
回答by Tometzky
If your database is not very big and you can afford some downtime then you can:
如果您的数据库不是很大并且您可以承受一些停机时间，那么您可以：
- Disable write access to the database
this is essential as otherwise any changes after starting the next point will be lost pg_dump --create --column-inserts databasename > databasename.pgdump.sql- Edit apropriate
CREATE TABLEstatement in databasename.pgdump.sql
If the file is too big for your editor just split it usingsplitcommand, edit, then assemble back usingcat drop database databasename
You do have a recent backup, just in case, do you?psql --single-transaction -f databasename.pgdump.sql
If you don't use--single-transactionit will be very slow
- 禁用对数据库的写访问，
这是必不可少的，否则开始下一个点后的任何更改都将丢失 pg_dump --create --column-inserts databasename > databasename.pgdump.sql- 编辑
CREATE TABLEdatabasename.pgdump.sql 中的适当语句
如果文件对您的编辑器来说太大，只需使用split命令将其拆分，编辑，然后使用cat drop database databasename
您确实有最近的备份，以防万一，是吗？psql --single-transaction -f databasename.pgdump.sql
如果你不使用--single-transaction它会很慢
If you use so called large objects make sure they are included in the dump. I'm not sure if they are by default in 8.1.
如果您使用所谓的大对象，请确保它们包含在转储中。我不确定它们是否在 8.1 中是默认的。
回答by Erwin Brandstetter
I have asked that question in pgsql-admin in 2007. Tom Lane himself declared it practically unfeasible to change the order in the catalogs.
我在 2007 年在 pgsql-admin 中问过这个问题。Tom Lane 本人宣称更改目录中的顺序实际上是不可行的。
Clarification: this applies for users with the present tools. Does not mean, it could not be implemented. IMO, it should be.
Still true for Postgres 12.
说明：这适用于使用当前工具的用户。并不意味着，它无法实施。海事组织，应该是。
对于 Postgres 12 仍然如此。
回答by Erwin Brandstetter
As the other answers mentioned, you cannot change the order of columns, that's up to postgres. You can (and should!) solve your problem with a view. For the purposes of your reporting query, it will look just like a table. Something like:
正如其他答案所提到的，您不能更改列的顺序，这取决于 postgres。您可以（并且应该！）通过视图解决您的问题。出于报告查询的目的，它看起来就像一个表格。就像是：
create view my_view as
select * from my_table
order by some_col;
回答by Vinko Vrsalovic
Specifying the column order in the query is the only reliable (and sane) way. That said, you can usually get a different ordering by altering the table as shown in the example below as the columns are usually (not guaranteed to be) returned in the order they were added to the table.
在查询中指定列顺序是唯一可靠（且理智）的方式。也就是说，您通常可以通过更改表来获得不同的排序，如下例所示，因为列通常（不保证）按照它们添加到表中的顺序返回。
postgres=# create table a(a int, b int, c int);
CREATE TABLE
postgres=# insert into a values (1,2,3);
INSERT 0 1
postgres=# select * from a;
a | b | c
---+---+---
1 | 2 | 3
(1 row)
postgres=# alter table a add column a2 int;
ALTER TABLE
postgres=# select * from a;
a | b | c | a2
---+---+---+----
1 | 2 | 3 |
(1 row)
postgres=# update a set a2 = a;
UPDATE 1
postgres=# alter table a drop column a;
ALTER TABLE
postgres=# alter table a rename column a2 to a;
ALTER TABLE
postgres=# select * from a;
b | c | a
---+---+---
2 | 3 | 1
(1 row)
postgres=#
回答by Turgs
I'm wanting the same. Yes, order isn't essential for my use-case, but it just rubs me the wrong way :)
我想要一样的。是的，订单对我的用例来说不是必需的，但它只会让我感到不适:)
What I'm doing to resolve it is as follows.
我正在做什么来解决它如下。
This method will ensure you KEEP any existing data,
此方法将确保您保留任何现有数据，
- Create a new version of the table using the ordering I want, using a temporary name.
- Insert all data into that new table from the existing one.
- Drop the old table.
- Rename the new table to the "proper name" from "temporary name".
- Re-add any indexes you previously had.
- Reset ID sequence for primary key increments.
- 使用临时名称，使用我想要的顺序创建表的新版本。
- 将所有数据从现有表插入到新表中。
- 放下旧桌子。
- 将新表从“临时名称”重命名为“专有名称”。
- 重新添加您以前拥有的任何索引。
- 重置主键增量的 ID 序列。
Current table order:
当前表顺序：
id, name, email
1. Create a new version of the table using the ordering I want, using a temporary name.
1. 使用临时名称，按照我想要的顺序创建表的新版本。
In this example, I want emailto be before name.
在这个例子中，我想email在name.
CREATE TABLE mytable_tmp
(
id SERIAL PRIMARY KEY,
email text,
name text
);
2. Insert all data into that new table from the existing one.
2. 将所有数据从现有表插入到新表中。
INSERT INTO mytable_tmp --- << new tmp table
(
id
, email
, name
)
SELECT
id
, email
, name
FROM mytable; --- << this is the existing table
3. Drop the old table.
3. 放下旧表。
DROP TABLE mytable;
4. Rename the new table to the "proper name" from "temporary name".
4.将新表从“临时名称”重命名为“专有名称”。
ALTER TABLE mytable_tmp RENAME TO mytable;
5. Re-add any indexes you previously had.
5. 重新添加您以前拥有的任何索引。
CREATE INDEX ...
6. Reset ID sequence for primary key increments.
6. 重置主键增量的 ID 序列。
SELECT setval('public.mytable_id_seq', max(id)) FROM mytable;
回答by Alex Willison
You can get the column ordering that you want by creating a new table and selecting columns of the old table in the order that you want them to present:
您可以通过创建一个新表并按照您希望它们显示的顺序选择旧表的列来获得所需的列排序：
CREATE TABLE test_new AS SELECT b, c, a FROM test;
SELECT * from test_new;
b | c | a
---+---+---
2 | 3 | 1
(1 row)
Note that this copies data only, not modifiers, constraints, indexes, etc..
请注意，这仅复制数据，而不是修饰符、约束、索引等。
Once the new table is modified the way you want, drop the original and alter the name of the new one:
按照您想要的方式修改新表后，删除原始表并更改新表的名称：
BEGIN;
DROP TABLE test;
ALTER TABLE test_new RENAME TO test;
COMMIT;
回答by Nick Johnson
Unfortunately, no, it's not. Column order is entirely up to Postgres.
不幸的是，不，它不是。列顺序完全取决于 Postgres。
