postgresql 如何使用 postgres 在表中的第 2 或第 3 列之后在表中添加新列？

How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?

提问by Elitmiar

How to add a new column in a table after the 2nd or 3rd column in the table using postgres?

如何使用postgres在表中的第二列或第三列之后在表中添加新列？

My code looks as follows

我的代码如下

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id

采纳答案by Milen A. Radev

No, there's no direct way to do that. And there's a reason for it - every query should list all the fields it needs in whatever order (and format etc) it needs them, thus making the order of the columns in one table insignificant.

不，没有直接的方法可以做到这一点。这是有原因的 - 每个查询都应该以它需要的任何顺序（和格式等）列出它需要的所有字段，从而使一个表中列的顺序变得无关紧要。

If you really need to do that I can think of one workaround:

如果您真的需要这样做，我可以想到一种解决方法：

• dump and save the description of the table in question (using pg_dump --schema-only --table=<schema.table> ...)
• add the column you want where you want it in the saved definition
• rename the table in the saved definition so not to clash with the name of the old table when you attempt to create it
• create the new table using this definition
• populate the new table with the data from the old table using 'INSERT INTO <new_table>SELECT field1, field2, <default_for_new_field>, field3,... FROM <old_table>';
• rename the old table
• rename the new table to the original name
• eventually drop the old, renamed table after you make sure everything's alright

• 转储并保存相关表的描述（使用pg_dump --schema-only --table=<schema.table> ...）
• 在保存的定义中添加所需的列
• 重命名已保存定义中的表，以免在尝试创建旧表时与旧表的名称发生冲突
• 使用此定义创建新表
• 使用'INSERT INTO <new_table>SELECT field1, field2, <default_for_new_field>, field3,... FROM <old_table>'用旧表中的数据填充新表；
• 重命名旧表
• 将新表重命名为原始名称
• 确保一切正常后，最终删除旧的、重命名的表

回答by Jeremy Gustie

The order of columns is not irrelevant, putting fixed width columns at the front of the table can optimize the storage layout of your data, it can also make working with your data easier outside of your application code.

列的顺序并不是无关紧要的，将固定宽度的列放在表格的前面可以优化数据的存储布局，它还可以在应用程序代码之外更轻松地处理数据。

PostgreSQL does not support altering the column ordering (see Alter column positionon the PostgreSQL wiki); if the table is relatively isolated, your best bet is to recreate the table:

PostgreSQL 不支持更改列顺序（请参阅PostgreSQL wiki 上的更改列位置）；如果表相对孤立，最好的办法是重新创建表：

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

If you have a lot of views or constraints defined against the table, you can re-add all the columns after the new column and drop the original columns (see the PostgreSQL wiki for an example).

如果您对表定义了很多视图或约束，则可以在新列之后重新添加所有列并删除原始列（请参阅 PostgreSQL wiki 的示例）。

回答by Evan Carroll

The real problem here is that it's not done yet. Currently PostgreSQL's logical ordering is the same as the physical ordering. That's problematic because you can't get a different logical ordering, but it's even worse because the table isn't physicallypacked automatically, so by moving columns you can get different performance characteristics.

这里真正的问题是它还没有完成。目前 PostgreSQL 的逻辑顺序与物理顺序相同。这是有问题的，因为您无法获得不同的逻辑顺序，但更糟糕的是，由于表在物理上并未自动打包，因此通过移动列，您可以获得不同的性能特征。

Arguing that it's that way by intent in designis pointless. It's somewhat likely to change at some point when an acceptable patch is submitted.

认为设计中的意图是这样是没有意义的。当提交可接受的补丁时，它可能会在某个时候发生变化。

All of that said, is it a good idea to rely on the ordinal positioning of columns, logical or physical? Hell no. In production code you should never be using an implicit ordering or *. Why make the code more brittle than it needs to be? Correctness should always be a higher priority than saving a few keystrokes.

综上所述，依靠列的顺序定位（逻辑或物理）是个好主意吗？一定不行。在生产代码中，您永远不应该使用隐式排序或*. 为什么要让代码比它需要的更脆弱？正确性应该始终比保存几次击键具有更高的优先级。

As a work around, you can in fact modify the column ordering by recreating the table, or through the "add and reorder" game

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

See also,

也可以看看，

• Column tetrisreordering in order to make things more space-efficient

• 列俄罗斯方块重新排序以提高空间效率

回答by Samuel Cunha

The column order is relevant to me, so I created this function. See if it helps. It works with indexes, primary key, and triggers. Missing Views and Foreign Key and other features are missing.

列顺序与我相关，所以我创建了这个函数。看看它是否有帮助。它适用于索引、主键和触发器。缺少视图和外键以及缺少其他功能。

Example:

例子：

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

Source code:

源代码：

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;

回答by Spanky Quigman

@Jeremy Gustie's solution above almost works, but will do the wrong thing if the ordinals are off (or fail altogether if the re-ordered ordinals make incompatible types match). Give it a try:

@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;

The results show the problem:

结果显示问题：

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

You can remedy this by specifying the column names in the insert:

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

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

That gives you what you really want:

这给了你你真正想要的：

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

The problem comes when you have legacy that doesn't do this, as I indicated above in my comment on peufeu's reply.

正如我在上面对 peufeu 回复的评论中指出的那样，当您拥有不这样做的遗产时，问题就会出现。

Update:It occurred to me that you can do the same thing with the column names in the INSERT clause by specifying the column names in the SELECT clause. You just have to reorder them to match the ordinals in the target table:

更新：我想到您可以通过在 SELECT 子句中指定列名来对 INSERT 子句中的列名做同样的事情。您只需要重新排序它们以匹配目标表中的序数：

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

And you can of course do both to be very explicit:

你当然可以同时做到非常明确：

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

That gives you the same results as above, with the column values properly matched.

这将为您提供与上述相同的结果，列值正确匹配。

回答by peufeu

The order of the columns is totally irrelevant in relational databases

列的顺序在关系数据库中完全无关

Yes.

是的。

For instance if you use Python, you would do :

例如，如果你使用 Python，你会这样做：

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

Or you would do :

或者你会这样做：

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

But no sane person would ever use positional results like this :

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

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

回答by Raithier

@ Milen A. Radev

@米伦A.拉德夫

The irrelevant need from having a set order of columns is not always defined by the query that pulls them. In the values from pg_fetch_rowdoes not include the associated column name and therefore would require the columns to be defined by the SQL statement.

具有一组列顺序的无关需求并不总是由提取它们的查询定义。来自的值pg_fetch_row不包括关联的列名，因此需要由 SQL 语句定义列。

A simple select * fromwould require innate knowledge of the table structure, and would sometimes cause issues if the order of the columns were to change.

一个简单的select * from将需要表结构的先天知识，如果列的顺序发生变化，有时会导致问题。

Using pg_fetch_associs a more reliable method as you can reference the column names, and therefore use a simple select * from.

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