如何更改 PostgreSQL 数据库表中列的位置？

我试过以下方法，但没有成功:

ALTER TABLE person ALTER COLUMN dob POSITION 37;
206041 次浏览
小开
最佳答案

PostgreSQL Wiki 中的“ 改变纵队位置”表示:

PostgreSQL 当前定义列 基于 attnum列的订单 pg_attribute表。唯一的办法 要更改列顺序，可以使用 重新创建表格，或者通过添加 列和旋转数据，直到 达到预期的布局。

这是相当弱的，但是在他们的辩护中，在标准 SQL 中，也没有重新定位列的解决方案。支持更改列的序号位置的数据库品牌正在定义 SQL 语法的扩展。

我想到了另一个主意: 您可以定义一个 VIEW，它指定您喜欢的列顺序，而不需要改变列在基表中的物理位置。

小开

我不认为你现在可以: 看看 这篇文章发表在 Postgreql wiki 上。

本文中的三个变通办法是:

  1. 重现这张桌子
  2. 添加列并移动数据
  3. 用视图隐藏差异。
小开

这个帖子很老了，可能已经解决了，但是我也有同样的问题。我通过创建指定新列顺序的原始表的视图来解决这个问题。

从这里，我可以使用视图，也可以从视图创建一个新表。

CREATE VIEW original_tab_vw AS
SELECT a.col1, a.col3, a.col4, a.col2
FROM original_tab a
WHERE a.col1 IS NOT NULL --or whatever

SELECT * INTO new_table FROM original_tab_vw

重命名或删除原始表，并将新表的名称设置为旧表。

小开

在 PGAdmin 中打开该表，并在底部的 SQL 窗格中复制 SQLCreateTable 语句。然后打开查询工具并粘贴。如果表中有数据，请将表名更改为‘ new _ name’，如果没有，请删除 Drop Table 行中的注释“——”。根据需要编辑列序列。注意最后一列中缺少/多余的逗号，以防您移动了它。执行新的 SQLCreateTable 命令。刷新，然后... 瞧。

对于设计阶段的空表，这种方法是非常实用的。

如果表中有数据，我们还需要重新排列数据的列序列。这很简单: 使用 INSERT将旧表导入到新版本中，包括:

INSERT INTO new ( c2, c3, c1 ) SELECT * from old;

... 其中 c2，c3，c1是旧表中的 c1，c2，c3列的新位置。请注意，在这种情况下，您 必须使用“新”名称为编辑的“旧”表，或 你会失去你的数据。如果列名很多，那么使用与上面相同的方法将新的表结构复制到文本编辑器中，并在那里创建新的列列表，然后将其复制到 INSERT语句中。

检查一切正常后，使用 ALTER TABLE new RENAME TO old;将旧表 DROP并将‘ new’名称更改为‘ old’，就完成了。

小开

一种方法是首先将整个数据库转储为数据，然后只转储模式(pg_dump -s databasename > databasename_schema.sql) ，尽管在必须绝对更改列顺序且使用外键时重新排列列是一种笨拙的选择。接下来编辑模式文件以按照您的喜好重新排列列，然后从模式中重新创建数据库，最后将数据还原到新创建的数据库中。

小开

在 PostgreSQL 中，当添加一个字段时，它将被添加到表的末尾。 如果我们需要插入到特定的位置

    alter table tablename rename to oldtable;
create table tablename (column defs go here); ### with all the constraints
insert into tablename (col1, col2, col3) select col1, col2, col3 from oldtable;
小开

我使用 Django，如果你不想头疼的话，它需要每个表中的 id 列。 不幸的是，我粗心了，我的表 bp.geo _ location _ ague 不包含这个字段。 我签了个小把戏。 第一步:

CREATE VIEW bp.geo_location_vague_vw AS
SELECT
a.id, -- I change order of id column here.
a.in_date,
etc
FROM bp.geo_location_vague a

步骤2: (不创建表-表将自动创建!)

SELECT * into bp.geo_location_vague_cp2 FROM bp.geo_location_vague_vw

第三步:

CREATE SEQUENCE bp.tbl_tbl_id_seq;
ALTER TABLE bp.geo_location_vague_cp2 ALTER COLUMN id SET DEFAULT nextval('tbl_tbl_id_seq');
ALTER SEQUENCE bp.tbl_tbl_id_seq OWNED BY bp.geo_location_vague_cp2.id;
SELECT setval('tbl_tbl_id_seq', COALESCE(max(id), 0)) FROM bp.geo_location_vague_cp2;

因为我需要在表中有大串行的伪类型。在 SELECT * 变成 pg 之后会创建大串行类型插入大串行。

第四步: 现在我们可以删除视图、删除源表并用旧名称重命名新表。 这个把戏成功地结束了。

小开

有一些变通办法可以使之成为可能:

  1. 重现整张桌子

  2. 在当前表中创建新列

  3. 创建视图

Https://tableplus.com/blog/2018/09/postgresql-is-it-possible-to-alter-column-order-position-in-a-table.html

小开

我当时正在重新排序大量的表格，不想一遍又一遍地编写相同的查询，所以我编写了一个脚本来完成这些工作。从本质上说，它是:

  1. 从 pg_dump获取表创建 SQL
  2. 从转储中获取所有可用列
  3. 按照所需的顺序放置列
  4. 修改原始 pg_dump查询以创建带有数据的重新排序表
  5. 扔掉旧桌子
  6. 重命名新表以匹配旧表

它可以通过运行以下简单命令来使用:

./reorder.py -n schema -d database table \
first_col second_col ... penultimate_col ultimate_col --migrate

它打印出 sql，这样您就可以验证和测试它，这是我基于 pg_dump的一个重要原因。你可以找到 我是 Github Repo。

小开

对于那些试图像这样改变列顺序的人来说，只需知道它不会起作用，因为整个表会被搞乱。您将收到这样的错误: [ XX000]错误: 无效的内存分配请求大小18446744073709551613

不幸的是，似乎 (咒语)不仅用于检索数据，还用于存储。

删除整个表的想法都很好，但你也必须删除所有的 FK，IX 等。我可能会学着把我的专栏放在后面。

select *
from information_schema.columns
where table_name = 'table1';


update pg_catalog.pg_attribute
set attnum = 10 where attname = 'column_on_9_position_to_move_to_7';


update pg_catalog.pg_attribute
set attnum = 9 where attname = 'column_on_7_position_to_move_to_9';


update pg_catalog.pg_attribute
set attnum = 7 where attname = 'column_on_9_position_to_move_to_7';

提交答案