是否可以在Postgres 8.1中更改列的自然顺序？
我知道不应该依赖列的顺序 - 这并不是我的关键 - 我只需要它以一种更令人愉悦的方式生成一些自动生成的内容，以便字段顺序从pgadmin一直匹配到后端再到前端。
9个回答
18
如果你的数据库不太大并且可以承受一些停机时间，那么你可以：
- 禁用对数据库的写访问
这是必要的，否则在开始下一个步骤后进行的任何更改都将丢失 pg_dump --create --column-inserts databasename > databasename.pgdump.sql- 编辑 databasename.pgdump.sql 中适当的
CREATE TABLE语句
如果文件太大无法在编辑器中打开，可以使用split命令拆分文件，编辑后使用cat合并回来 drop database databasename
你有最近的备份，以防万一，对吧？psql --single-transaction -f databasename.pgdump.sql
如果不使用--single-transaction会非常慢
如果使用所谓的大型对象，请确保它们包含在转储文件中。我不确定它们是否默认包含在 8.1 版本中。
- Tometzky
1
这在9.3版本中运行得非常好。请注意，如果您想导入到一个新的数据库中，您可以更改转储文件中涉及数据库名称的三行代码。 - Gerry Shaw
18
您实际上可以直接更改列的顺序，但我强烈不建议这样做，如果您决定这样做，一定要非常小心。
例如：
# 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用户连接到数据库，以便可以修改系统表。
例如：
# 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是唯一的列，因此在修改列号时需要使用临时值，如下所示：
# 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)
例如：
# 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用户连接到数据库，以便可以修改系统表。
例如：
# 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是唯一的列，因此在修改列号时需要使用临时值，如下所示：
# 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)
请注意，由于此操作涉及到数据库系统表的操作，请谨慎进行，仅在必要情况下执行。
截至 postgres 8.3 版本，此方法可行。但对于更早版本，请自行斟酌。
- Russell
8
7这很棘手，几个系统对象引用了列号。当你交换第2和第3列时，如果一个对象需要从第2列获取信息，你将会遇到麻烦。现在这是第3列，你的数据库已经损坏。看看pg_constraint，它可以拯救你的数据库。 - Frank Heikens
7这样做会导致您表中的所有数据丢失。 - Peter Eisentraut
9除了不被支持之外，这种技术会交换列名而不考虑它们的内容，这与问题所要求的并不相符（尽管答案已被接受）。如果确实需要这样做，应该使用
ALTER TABLE... RENAME COLUMN...，因为它可以可靠地实现相同的结果。 - Daniel Vérité1实际尝试过。表变得无用，每次查询表都会断开服务器连接。好的一点是，如果你回退一切，一切又能正常工作了。 - cachique
3@x-yuri：不，这个建议是积极有害的，应该被删除。它可能看起来像是有效的，但会造成损害，直到无法逆转为止。 - Erwin Brandstetter
显示剩余3条评论
13
我在2007年在pgsql-admin中询问了这个问题。 Tom Lane本人宣称在目录中更改顺序实际上是不可行的。
澄清一下：这适用于使用当前工具的用户。这并不意味着无法实现。在我看来，应该这样做。
对于Postgres 12仍然是正确的。
- Erwin Brandstetter
4
3允许重新排序列位置的想法并不是PostgreSQL开发人员反对的，更多的情况是没有人愿意来做这项工作。如果有人想要添加该功能……该文章末尾已经概述了起始步骤。 - knowledge_is_power
4虽然大家都知道这一点，但我赞同这种做法。从Igor Neyman的回答中，似乎有些人没有理由支持它。我认为在某些情况下，这个功能是很有用的，特别是对于像我这样有强迫症倾向的人。正如Tom Lane所指出的那样，我们还需要修改“视图、外键、索引、默认值、规则等”以及所有依赖于列的其他内容。即使只使用PL/pgSQL，这实际上并不难想象。我觉得我们中大多数人在遇到这个问题时都会感到“如果我有时间就好了”。 - Greg Kramida
1谢谢你的提示，我想对于Postgres 11（以及之后的版本）仍然有效。 - leole
1@leole：是的，仍然正确。 - Erwin Brandstetter
8
我也希望如此。是的，对于我的应用场景而言，订单并不重要，但这确实让我感到不适。
我的解决办法如下：
此方法将确保您保留任何现有数据，
- 使用临时名称创建一个新版本的表格，按照所需顺序进行排序。
- 从现有表中将所有数据插入到新表中。
- 删除旧表。
- 将新表从“临时名称”改为“正确名称”。
- 重新添加之前拥有的任何索引。
- 重置用于主键增量的ID序列。
当前表格顺序：
id, name, email
1. 使用我想要的顺序创建一个新版本的表格，使用临时名称。
在这个例子中，我想让email出现在name之前。
CREATE TABLE mytable_tmp
(
id SERIAL PRIMARY KEY,
email text,
name text
);
2.将现有表中的所有数据插入到新表中。
INSERT INTO mytable_tmp --- << new tmp table
(
id
, email
, name
)
SELECT
id
, email
, name
FROM mytable; --- << this is the existing table
3. 删除旧表。
DROP TABLE mytable;
4. 将新表从“临时名称”重命名为“正确名称”。
ALTER TABLE mytable_tmp RENAME TO mytable;
5. 重新添加之前的任何索引。
CREATE INDEX ...
6. 重置主键增量的ID序列。
SELECT setval('public.mytable_id_seq', max(id)) FROM mytable;
- Turgs
1
这太糟糕了。维护表格组织需要大量的时间和精力。 - Jacob Thomason
4
如何在PostgreSQL中重新排列列
警告：该方法会删除表属性，例如唯一索引和其他在drop your_table时无意中产生的影响。因此，您需要在重新排列列之后添加这些属性。
--create a table where column bar comes before column baz:
CREATE TABLE foo ( moo integer, bar character varying(10), baz date );
--insert some data
insert into foo (moo, bar, baz) values (34, 'yadz', now());
insert into foo (moo, bar, baz) values (12, 'blerp', now());
select * from foo;
┌─────┬───────┬────────────┐
│ moo │ bar │ baz │
├─────┼───────┼────────────┤
│ 34 │ yadz │ 2021-04-07 │
│ 12 │ blerp │ 2021-04-07 │
└─────┴───────┴────────────┘
-- Define your reordered columns here, don't forget one,
-- or it'll be missing from the replacement.
drop view if exists my_view;
create view my_view as ( select moo, baz, bar from foo );
select * from my_view;
DROP TABLE IF EXISTS foo2;
--foo2 is your replacement table that has columns ordered correctly
create table foo2 as select * from my_view;
select * from foo2;
--finally drop the view and the original table and rename
DROP VIEW my_view;
DROP TABLE foo;
ALTER TABLE foo2 RENAME TO foo;
--observe the reordered columns:
select * from foo;
┌─────┬────────────┬───────┐
│ moo │ baz │ bar │
├─────┼────────────┼───────┤
│ 34 │ 2021-04-07 │ yadz │
│ 12 │ 2021-04-07 │ blerp │
└─────┴────────────┴───────┘
获取列名的先前顺序以便复制和粘贴
如果您要重新排序的表格具有数百列，您将希望自动获取给定列的顺序，这样您就可以复制、微调，然后粘贴到上面的视图中。
SELECT string_agg(column_name, ',') from (
select * FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_big_table'
order by ordinal_position asc
) f1;
这将打印：
column_name_1,column_name_2, ..., column_name_n
您可以复制上面命名的排序，将它们移动到它们所属的位置，然后粘贴到顶部视图中。
- Please delete this account
2
2这并不是改变列的顺序，而是根据某一列改变数据的顺序。 - rjmunro
我认为你的想法是正确的，可以使用视图重新组织表格的列。然后，你可以选择直接使用该视图或从视图创建一个新表。但是需要注意，新表将缺少索引和键。详情请参考：https://dev59.com/bHVC5IYBdhLWcg3wdw65#21028894 - Ken
1
您可以通过创建一个新表，在所需顺序中选择旧表的列，以获得您想要的列排序：
请注意，这只复制数据，而不包括修饰符、约束、索引等。
一旦新表按您想要的方式进行修改，删除原始表并更改新表的名称：
CREATE TABLE test_new AS SELECT b, c, a FROM test;
SELECT * from test_new;
b | c | a
---+---+---
2 | 3 | 1
(1 row)
请注意，这只复制数据，而不包括修饰符、约束、索引等。
一旦新表按您想要的方式进行修改，删除原始表并更改新表的名称：
BEGIN;
DROP TABLE test;
ALTER TABLE test_new RENAME TO test;
COMMIT;
- Alex Willison
0
在查询中指定列的顺序是唯一可靠（和明智）的方法。话虽如此，通过像下面示例中所示的更改表来更改列的顺序通常可以获得不同的排序方式，因为这些列通常（但不能保证）按照它们添加到表中的顺序返回。
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=#
- Vinko Vrsalovic
3
我不是在编写查询。我正在使用一个自动生成查询和表单等内容的产品。生成后我可以进行编辑，但我更愿意重新生成而不必再次编辑。 - rjmunro
然后询问产品提供商有没有指定列顺序的方法（或者请求他们将其添加为一个功能）。 - Vinko Vrsalovic
据我所知，PostgreSQL开发人员不想添加这个功能 :( - grom
0
不要犯跟随被接受答案的错误。那会破坏你的数据库。看看这个：
发生了什么事？PostgreSQL服务器崩溃了，因为现有的表行中存储的是一个整数，而根据pg_attribute的说法，应该是一个文本。
正如Erwin Brandstetter的回答所说（应该是被接受的答案），在PostgreSQL中没有改变列顺序的方法。但是，改变列顺序并不是必要的，因为关系数据库不是电子表格，表格不需要为交互用户“好看”。
以下是两个建议，如果您不喜欢列顺序：
test=# CREATE TABLE mangle_me (a integer, b text);
CREATE TABLE
test=# INSERT INTO mangle_me VALUES (1, 'a text');
INSERT 0 1
test=# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid = 'mangle_me'::regclass;
attrelid │ attname │ attnum
══════════╪══════════╪════════
27035 │ tableoid │ -6
27035 │ cmax │ -5
27035 │ xmax │ -4
27035 │ cmin │ -3
27035 │ xmin │ -2
27035 │ ctid │ -1
27035 │ a │ 1
27035 │ b │ 2
(8 rows)
test=# UPDATE pg_attribute SET attnum = 4 WHERE attrelid = 27035 AND attname = 'a';
UPDATE 1
test=# UPDATE pg_attribute SET attnum = 1 WHERE attrelid = 27035 AND attname = 'b';
UPDATE 1
test=# UPDATE pg_attribute SET attnum = 2 WHERE attrelid = 27035 AND attname = 'a';
UPDATE 1
test=# TABLE mangle_me;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
!?>
发生了什么事？PostgreSQL服务器崩溃了，因为现有的表行中存储的是一个整数，而根据pg_attribute的说法，应该是一个文本。
正如Erwin Brandstetter的回答所说（应该是被接受的答案），在PostgreSQL中没有改变列顺序的方法。但是，改变列顺序并不是必要的，因为关系数据库不是电子表格，表格不需要为交互用户“好看”。
以下是两个建议，如果您不喜欢列顺序：
在表上创建一个具有不同列顺序的视图。
您可以像使用表一样使用该视图；它将接受
INSERT、UPDATE和DELETE操作。如果您坚持要改变列的顺序，并且不想有长时间的停机时间，唯一的方法是创建一个逻辑备用数据库，其中列的顺序已经改变。一旦逻辑复制追赶上来，您可以将应用程序切换到逻辑备用数据库，并删除原始数据库。
这带来了逻辑复制的所有困难：
DDL不会被复制
在繁忙的数据库上，逻辑复制可能太慢，永远无法追赶上
- Laurenz Albe
-1
很遗憾，不是这样的。列的顺序完全由Postgres决定。
- Arachnid
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接