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

How do I alter the position of a column in a PostgreSQL database table?

提问by Huuuze

I've tried the following, but I was unsuccessful:

我尝试了以下方法，但没有成功：

ALTER TABLE person ALTER COLUMN dob POSITION 37;

采纳答案by Bill Karwin

"Alter column position" in the PostgreSQL Wiki says:

PostgreSQL Wiki 中的“更改列位置”说：

PostgreSQL currently defines column order based on the attnumcolumn of the pg_attributetable. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout.

PostgreSQL 目前根据表的attnum列定义列顺序pg_attribute。更改列顺序的唯一方法是重新创建表，或者添加列并旋转数据直到达到所需的布局。

That's pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.

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

One other idea occurs to me: you can define a VIEWthat specifies the order of columns how you like it, without changing the physical position of the column in the base table.

我想到了另一个想法：您可以定义一个VIEW指定您喜欢的列的顺序，而无需更改基表中列的物理位置。

回答by Allwin

In PostgreSQL, while adding a field it would be added at the end of the table. If we need to insert into particular position then

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

alter table tablename rename to oldtable;
create table tablename (column defs go here);
insert into tablename (col1, col2, col3) select col1, col2, col3 from oldtable;

回答by Ken

This post is old and probably solved but I had the same issue. I resolved it by creating a view of the original table specifying the new column order.

这篇文章很旧，可能已经解决了，但我遇到了同样的问题。我通过创建指定新列顺序的原始表的视图来解决它。

From here I could either use the view or create a new table from the view.

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

    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

Rename or drop the original table and set the name of the new table to the old table.

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

回答by Ville

One, albeit a clumsy option to rearrange the columns when the column order must absolutely be changed, and foreign keys are in use, is to first dump the entire database with data, then dump just the schema (pg_dump -s databasename > databasename_schema.sql). Next edit the schema file to rearrange the columns as you would like, then recreate the database from the schema, and finally restore the data into the newly created database.

一个，尽管在列顺序必须绝对改变并且使用外键时重新排列列的笨拙选项是首先转储整个数据库的数据，然后只转储模式 ( pg_dump -s databasename > databasename_schema.sql)。接下来编辑架构文件以根据需要重新排列列，然后从架构重新创建数据库，最后将数据恢复到新创建的数据库中。

回答by Mike Woodhouse

I don't think you can at present: see this article on the Postgresql wiki.

我认为您目前不能：请参阅Postgresql wiki 上的这篇文章。

The three workarounds from this article are:

本文中的三种解决方法是：

1. Recreate the table
2. Add columns and move data
3. Hide the differences with a view.

1. 重新创建表
2. 添加列并移动数据
3. 用视图隐藏差异。

回答by marcopolo

Open the table in PGAdmin and in the SQL pane at the bottom copy the SQL Create Table statement. Then open the Query Tool and paste. If the table has data, change the table name to 'new_name', if not, delete the comment "--" in the Drop Table line. Edit the column sequence as required. Mind the missing/superfluous comma in the last column in case you have moved it. Execute the new SQL Create Table command. Refresh and ... voilà.

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

For empty tables in the design stage this method is quite practical.

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

In case the table has data, we need to rearrange the column sequence of the data as well. This is easy: use INSERTto import the old table into its new version with:

如果表中有数据，我们也需要重新排列数据的列顺序。这很简单：使用以下INSERT命令将旧表导入到新版本中：

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

... where c2, c3, c1are the columns c1, c2, c3of the old table in their new positions. Please note that in this case you must use a 'new' namefor the edited 'old' table, or you will lose your data. In case the column names are many, long and/or complex use the same method as above to copy the new table structure into a text editor, and create the new column list there before copying it into the INSERTstatement.

...其中c2，c3，c1是列c1，c2，c3旧表在其新职位。请注意，在这种情况下，您必须为已编辑的“旧”表使用“新”名称，否则您将丢失数据。如果列名很多、很长和/或很复杂，请使用与上述相同的方法将新表结构复制到文本编辑器中，并在将其复制到INSERT语句之前在那里创建新列列表。

After checking that all is well, DROPthe old table and change the the 'new' name to 'old' using ALTER TABLE new RENAME TO old;and you are done.

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

回答by GammaGames

I was working on re-ordering a lot of tables and didn't want to have to write the same queries over and over so I made a script to do it all for me. Essentially, it:

我正在对很多表进行重新排序，并且不想一遍又一遍地编写相同的查询，因此我制作了一个脚本来为我完成所有工作。本质上，它：

1. Gets the table creation SQL from pg_dump
2. Gets all available columns from the dump
3. Puts the columns in the desired order
4. Modifies the original pg_dumpquery to create a re-ordered table with data
5. Drops old table
6. Renames new table to match old table

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

It can be used by running the following simple command:

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

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

It prints out the sql so you can verify and test it, that was a big reason I based it on pg_dump. You can find the github repo here.

它打印出 sql 以便您可以验证和测试它，这是我基于pg_dump. 您可以在此处找到github 存储库。

回答by Orlov Const

I use Django and it requires id column in each table if you don't want to have a headache. Unfortunately, I was careless and my table bp.geo_location_vague didn't contain this field. I initialed little trick. Step 1:

我使用 Django，如果您不想头疼，它需要每个表中的 id 列。不幸的是，我粗心大意，我的表 bp.geo_location_vague 不包含此字段。我草签了小技巧。第1步：

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

Step 2: (without create table - table will create automaticaly!)

第 2 步：（不创建表 - 表将自动创建！）

SELECT * into bp.geo_location_vague_cp2 FROM bp.geo_location_vague_vw

Step 3:

第 3 步：

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;

Because I need have bigserial pseudotype in the table. After SELECT * into pg will create bigint type insetad bigserial.

因为我需要在表中有 bigserial 伪类型。SELECT * into pg 后将创建 bigint 类型 insetad bigserial。

step 4: Now we can drop the view, drop source table and rename the new table in the old name. The trick was ended successfully.

第 4 步：现在我们可以删除视图、删除源表并以旧名称重命名新表。骗局成功结束。

回答by almoglan

There are some workarounds to make it possible:

有一些解决方法可以使其成为可能：

1. Recreating the whole table

2. Create new columns within the current table

3. Create a view

1. 重新创建整个表

2. 在当前表中创建新列

3. 创建视图

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

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