如何使用 postgres 在表格的第2或第3列之后添加一个新的列？

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

我的代码如下所示

ALTER TABLE n_domains ADD COLUMN contract_nr int after owner_id
109133 次浏览
小开
最佳答案

不，没有直接的方法。这是有原因的——每个查询都应该按照需要的顺序(和格式等)列出所有需要的字段，这样一来，一个表中列的顺序就不重要了。

如果你真的需要这么做，我可以想出一个解决办法:

  • 转储并保存有关表的描述(使用 pg_dump --schema-only --table=<schema.table> ...)
  • 在保存的定义中添加所需的列
  • 在保存的定义中重命名表，以便在尝试创建旧表时不与其名称冲突
  • 使用此定义创建新表
  • 使用“ INSERT INTO <new_table> SELECT field1，field2，<default_for_new_field>，field3，... FROM <old_table>”用旧表中的数据填充新表;
  • 重新命名旧表
  • 将新表重命名为原始名称
  • 在确保一切正常之后，最终放弃旧的、重命名的表
小开

在关系数据库中，列的顺序是完全不相关的

是的。

例如，如果你使用 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
小开

列的顺序并不是不相关的，将固定宽度的列放在表的前面可以优化数据的存储布局，它还可以使在应用程序代码之外处理数据更加容易。

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;

如果针对表定义了大量视图或约束，则可以在新列之后重新添加所有列，并删除原始列(参见 PostgreSQL wiki 的示例)。

小开

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

问题出现在你的遗产没有做到这一点，正如我上面在我的评论中对佩弗的回复所指出的。

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

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

当然，你可以同时做这两件事，以便非常明确地表达:

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

这将得到与上面相同的结果，并且列值正确匹配。

小开

@ Milen A. Radev

对列的设置顺序的不相关需求并不总是由提取它们的查询定义的。中的值不包括相关联的列名，因此需要由 SQL 语句定义这些列。

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

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

小开

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

认为 故意设计的就是这样毫无意义。当提交一个可接受的补丁时，它有可能在某个时候发生改变。

综上所述，依赖于列的顺序定位(逻辑上的还是物理上的)是一个好主意吗？当然没有。在生产代码中，永远不应该使用隐式排序或 *。为什么要让代码变得更脆弱呢？正确性应该总是比保存几次击键优先。

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

还有,

  • 列 tetris 重新排序，以使事情更加节省空间
小开

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

例如:

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;
小开

对于 DBA 来说，这是一个可视化的好东西，可以在引擎中实现，只会带来较小的性能损失。将列顺序表添加到 pg_catalog或它最适合的位置。将它保存在内存中，并在某些查询之前使用它。为什么这么小题大做。


提交答案