如何在PostgreSQL中在第二或第三列后向表中添加新列？

Question

如何在PostgreSQL中的表中第二或第三列后添加新列？

我的代码如下所示：

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id

- Elitmiar

---

关系型数据库中列的顺序是无关紧要的，它只涉及到在工具中如何展示。数据库表格本身并没有对列进行排序。 - marc_s

@marc_s 的说法不正确。物理顺序与性能相关，并且当前的 PostgreSQL 使用相同的物理和逻辑顺序。https://wiki.postgresql.org/wiki/Alter_column_position - Paul Draper

Answer 1

不，没有直接的方法可以实现这个功能。这是有原因的——每个查询都应该按照所需的顺序列出所有需要的字段（以及格式等），从而使得表中列的顺序变得无关紧要。

如果你确实需要这样做，我可以想到一个解决方法：

• 使用pg_dump --schema-only --table=<schema.table> ...将相关表的描述信息进行导出并保存
• 在保存的定义中添加所需的新列
• 将保存的定义中的表名更改为避免与旧表重名
• 使用此定义创建新表
• 使用‘INSERT INTO <new_table> SELECT field1, field2, <default_for_new_field>, field3,... FROM <old_table>’将旧表中的数据填充到新表中
• 将旧表重命名
• 将新表重命名为原始表名
• 确认一切正常后，删除旧表

- Milen A. Radev

---

每个查询都应按任何顺序列出它所需的所有字段。只要您不必与其他人一起工作，这很容易做到。 - Mark E. Haase

虽然数据库使用应用程序可能不关心列的顺序，但当DBA调查表结构或数据时，如果相似的列按顺序列出会更加美观。我个人发现很烦恼的是，由于后来添加，我有几个时间列没有视觉上分组。仅仅因为Postgres不支持在特定位置添加列就重新创建数据库感觉很奇怪，尤其是如果背后的原因是强制人们在查询中显式列出列。明确一点：我不认为这是原因。 - Dynom

不，没有直接的方法可以做到那一点。这样做是有原因的。根据https://wiki.postgresql.org/wiki/Alter_column_position，这是不正确的。 "允许重新排序列位置的想法并不是postgresql开发人员反对的，更多的是没有人站出来完成这项工作...... 能够修改列位置的两个主要原因是：通过将固定大小的列放在表的开头来优化物理布局...... 排序列可以使与表的操作更加容易..." 此外，该页面已经被查看了67k次 :) - Nathan Long

如果旧表有多个列被其他表引用，该怎么办？ - Soul Clinic

Answer 2

列的顺序非常重要，将固定宽度的列放在表的前面可以优化数据存储布局，还可以使在应用程序之外处理数据变得更加容易。 PostgreSQL不支持修改列的顺序（请参见PostgreSQL维基上的Alter column position）; 如果表相对独立，则最好的选择是重新创建表：

CREATE TABLE foobar_new ( ... );
INSERT INTO foobar_new SELECT ... FROM foobar;
DROP TABLE foobar CASCADE;
ALTER TABLE foobar_new RENAME TO foobar;

如果您的表中定义了许多视图或约束条件，可以在新列之后重新添加所有列，然后删除原始列（请参阅PostgreSQL维基百科上的示例）。

- Jeremy Gustie

Answer 3

这里的实际问题是它还没完成。目前PostgreSQL的逻辑排序与物理排序相同，这是有问题的，因为你无法获得不同的逻辑排序，但更糟糕的是表并没有自动紧凑，所以通过移动列可以获得不同的性能特性。

争论它是设计意图也毫无意义。当一个可接受的补丁被提交时，它很可能在某个时候发生改变。

尽管如此，在生产代码中依赖于列的序数位置（逻辑或物理）是一个好主意吗？当然不是。你永远不应该使用隐含的排序或*。为什么要让代码变得比必须的更脆弱呢？正确性应该始终比节省一些按键次数更重要。

作为解决方法，您实际上可以通过重新创建表，或通过“添加和重新排序”游戏来修改列的排序。

另请参见：

• 列俄罗斯方块重新排序以使事物更加节省空间

- Evan Carroll

Answer 4

对于我来说，列的顺序很重要，因此我创建了这个函数。看看它是否有帮助。它适用于索引、主键和触发器。缺少视图、外键和其他功能。

示例：

SELECT xaddcolumn('table', 'col3 int NOT NULL DEFAULT 0', 'col2');

源代码：

CREATE OR REPLACE FUNCTION xaddcolumn(ptable text, pcol text, pafter text)  RETURNS void AS $BODY$
DECLARE
    rcol RECORD;
    rkey RECORD;
    ridx RECORD;
    rtgr RECORD;
    vsql text;
    vkey text;
    vidx text;
    cidx text;
    vtgr text;
    ctgr text;
    etgr text;
    vseq text;
    vtype text;
    vcols text;
BEGIN
    EXECUTE 'CREATE TABLE zzz_' || ptable || ' AS SELECT * FROM ' || ptable;
    --colunas
    vseq = '';
    vcols = '';
    vsql = 'CREATE TABLE ' || ptable || '(';
    FOR rcol IN SELECT column_name as col, udt_name as coltype, column_default as coldef,
        is_nullable as is_null, character_maximum_length as len,
        numeric_precision as num_prec, numeric_scale as num_scale
        FROM information_schema.columns
        WHERE table_name = ptable
        ORDER BY ordinal_position
    LOOP
        vtype = rcol.coltype;
        IF (substr(rcol.coldef,1,7) = 'nextval') THEN
            vtype = 'serial';
            vseq = vseq || 'SELECT setval(''' || ptable || '_' || rcol.col || '_seq'''
                || ', max(' || rcol.col || ')) FROM ' || ptable || ';';
        ELSIF (vtype = 'bpchar') THEN
            vtype = 'char';
        END IF;
        vsql = vsql || E'\n' || rcol.col || ' ' || vtype;
        IF (vtype in ('varchar', 'char')) THEN
            vsql = vsql || '(' || rcol.len || ')';
        ELSIF (vtype = 'numeric') THEN
            vsql = vsql || '(' || rcol.num_prec || ',' || rcol.num_scale || ')';
        END IF;
        IF (rcol.is_null = 'NO') THEN
            vsql = vsql || ' NOT NULL';
        END IF;
        IF (rcol.coldef <> '' AND vtype <> 'serial') THEN
            vsql = vsql || ' DEFAULT ' || rcol.coldef;
        END IF;
        vsql = vsql || E',';
        vcols = vcols || rcol.col || ',';
        --
        IF (rcol.col = pafter) THEN
            vsql = vsql || E'\n' || pcol || ',';
        END IF;
    END LOOP;
    vcols = substr(vcols,1,length(vcols)-1);
    --keys
    vkey = '';
    FOR rkey IN SELECT constraint_name as name, column_name as col
        FROM information_schema.key_column_usage
        WHERE table_name = ptable
    LOOP
        IF (vkey = '') THEN
            vkey = E'\nCONSTRAINT ' || rkey.name || ' PRIMARY KEY (';
        END IF;
        vkey = vkey || rkey.col || ',';
    END LOOP;
    IF (vkey <> '') THEN
        vsql = vsql || substr(vkey,1,length(vkey)-1) || ') ';
    END IF;
    vsql = substr(vsql,1,length(vsql)-1) || ') WITHOUT OIDS';
    --index
    vidx = '';
    cidx = '';
    FOR ridx IN SELECT s.indexrelname as nome, a.attname as col
        FROM pg_index i LEFT JOIN pg_class c ON c.oid = i.indrelid
        LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
        LEFT JOIN pg_stat_user_indexes s USING (indexrelid)
        WHERE c.relname = ptable AND i.indisunique != 't' AND i.indisprimary != 't'
        ORDER BY s.indexrelname
    LOOP
        IF (ridx.nome <> cidx) THEN
            IF (vidx <> '') THEN
                vidx = substr(vidx,1,length(vidx)-1) || ');';
            END IF;
            cidx = ridx.nome;
            vidx = vidx || E'\nCREATE INDEX ' || cidx || ' ON ' || ptable || ' (';
        END IF;
        vidx = vidx || ridx.col || ',';
    END LOOP;
    IF (vidx <> '') THEN
        vidx = substr(vidx,1,length(vidx)-1) || ')';
    END IF;
    --trigger
    vtgr = '';
    ctgr = '';
    etgr = '';
    FOR rtgr IN SELECT trigger_name as nome, event_manipulation as eve,
        action_statement as act, condition_timing as cond
        FROM information_schema.triggers
        WHERE event_object_table = ptable
    LOOP
        IF (rtgr.nome <> ctgr) THEN
            IF (vtgr <> '') THEN
                vtgr = replace(vtgr, '_@eve_', substr(etgr,1,length(etgr)-3));
            END IF;
            etgr = '';
            ctgr = rtgr.nome;
            vtgr = vtgr || 'CREATE TRIGGER ' || ctgr || ' ' || rtgr.cond || ' _@eve_ '
                || 'ON ' || ptable || ' FOR EACH ROW ' || rtgr.act || ';';
        END IF;
        etgr = etgr || rtgr.eve || ' OR ';
    END LOOP;
    IF (vtgr <> '') THEN
        vtgr = replace(vtgr, '_@eve_', substr(etgr,1,length(etgr)-3));
    END IF;
    --exclui velha e cria nova
    EXECUTE 'DROP TABLE ' || ptable;
    IF (EXISTS (SELECT sequence_name FROM information_schema.sequences
        WHERE sequence_name = ptable||'_id_seq'))
    THEN
        EXECUTE 'DROP SEQUENCE '||ptable||'_id_seq';
    END IF;
    EXECUTE vsql;
    --dados na nova
    EXECUTE 'INSERT INTO ' || ptable || '(' || vcols || ')' ||
        E'\nSELECT ' || vcols || ' FROM zzz_' || ptable;
    EXECUTE vseq;
    EXECUTE vidx;
    EXECUTE vtgr;
    EXECUTE 'DROP TABLE zzz_' || ptable;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

- Samuel Cunha

---

我不得不稍微修改它，但不幸的是它不能处理像“placeId”这样的列名...我已经从rtgr中删除了, condition_timing as cond和vtgr中的|| ' ' || rtgr.cond添加了|| ' CASCADE'以处理在EXECUTE 'DROP TABLE ' || ptable;上链接的表顺便说一句，这个好用的函数似乎只能处理小写列名... - Jiab77

无论如何，@Samuel Cunha感谢分享，但如果您能解决列名问题，那就太好了。我自己不太擅长postgresql。 - Jiab77

Answer 5

@Jeremy Gustie的解决方案基本上是可行的，但如果序数不正确（或者重新排序的序数使不兼容的类型匹配失败），它将执行错误操作。试一试：

CREATE TABLE test1 (one varchar, two varchar, three varchar);
CREATE TABLE test2 (three varchar, two varchar, one varchar);
INSERT INTO test1 (one, two, three) VALUES ('one', 'two', 'three');
INSERT INTO test2 SELECT * FROM test1;
SELECT * FROM test2;

结果显示问题：

testdb=> select * from test2;
 three | two |  one
-------+-----+-------
 one   | two | three
(1 row)

您可以通过在插入中指定列名来解决这个问题：

INSERT INTO test2 (one, two, three) SELECT * FROM test1;

这将给你真正想要的东西：

testdb=> select * from test2;
 three | two | one
-------+-----+-----
 three | two | one
(1 row)

问题出现在您有一些旧的遗留代码，它们没有这样做，就像我在对peufeu回复的评论中所指出的那样。

更新：我想到了一个方法，您可以通过在SELECT子句中指定列名来使用INSERT子句中的相同列名。您只需要重新排序它们以匹配目标表中的序数即可。

INSERT INTO test2 SELECT three, two, one FROM test1;

当然，你可以同时进行两种方式以使得非常明确：

INSERT INTO test2 (one, two, three) SELECT one, two, three FROM test1;

这将给你与上述相同的结果，其中列值正确匹配。

- Spanky Quigman

Answer 6

在关系型数据库中，列的顺序完全无关紧要。

是的。

例如，如果您使用Python，可以这样做：

cursor.execute( "SELECT id, name FROM users" )
for id, name in cursor:
    print id, name

或者你可以这样做：

cursor.execute( "SELECT * FROM users" )
for row in cursor:
    print row['id'], row['name']

但是没有理智的人会像这样使用位置结果：

cursor.execute( "SELECT * FROM users" )
for id, name in cursor:
   print id, name

- bobflux

---

这完全是不正确的。你展示的方式适用于选择(select)的情况，但有时会看到没有指定列名的插入(insert)，例如：insert into table values(1, 2, 3, 4)。如果表格列的顺序发生变化，那么这种样式的插入查询将失败。值得注意的是，并非每个开发人员都是理智的，如果你继承了来自这些开发人员之一的代码... - Spanky Quigman

@SpankyQuigman 这样做完全是疯狂的，这正是 peufeu 所说的。 - Evan Carroll

Answer 7

嗯，这对于数据库管理员来说是一个视觉上的好东西，并且可以在引擎中实现，只会有轻微的性能损失。将列顺序表添加到pg_catalog或最适合的位置。将其保留在内存中，并在某些查询之前使用它。为什么要过度思考这样一个小的视觉效果。

- Ako

Answer 8

@ Milen A. Radev

有时候，需要按照一定的列顺序排列并不是由检索它们的查询所定义的。在从pg_fetch_row返回的值中，不包括相关的列名，因此需要通过SQL语句来定义列。

一个简单的select * from需要对表结构有内在的了解，并且如果列的顺序发生变化，有时会导致问题。

使用pg_fetch_assoc是一种更可靠的方法，因为您可以引用列名，因此可以使用简单的select * from。

- Raithier

---

这个需求并不无关紧要。当我手动检查数据时，我想简单地使用 SELECT *，但我希望更有趣的列能够排在前面。 - maaartinus