PostgreSQL插入数据到表格中从另一个表格中选择的数据

Question

我有两张表table1和test_table1，它们具有相同的模式。

这两个表都有行/数据和以1开头的主键id。

我想要做的是：

insert into test_table1 select * from table1;

但由于表1中的主键值存在于test_table1中，因此此方法失败了。

解决方法是指定列并将pk列留空，但出于某种原因，这也不起作用:

例如： 注意-以下查询中没有pk列

insert into test_table1 (col1, col2,..., coln) select col1,col2,...,coln from table1;

返回值

错误：违反唯一约束条件“test_table1_pkey”的重复关键字值 详细信息：(id)=(1)的键已经存在。

我知道这在MySql中可以工作，这只是因为Postgresql吗？有什么办法解决吗？

编辑：

两个表都有主键和序列集。

由于不清楚 - 表格没有相同的数据。 我只想将table1中的行添加到test_table1中。

对于告诉我从查询中排除主键的答案 - 我像我之前说的那样做了。

- Jerry Smith

---

哪个版本？如果您使用的是9.5，则INSERT .. ON CONFLICT可能适用于您。 - Nick

您已经定义了表的主键并向其中插入一个已经存在的值。这在 MySQL 中可以正常工作，因为您的MySQL表没有定义唯一的主键。请提供您测试表的表模式（包括约束），以获得完整的答案。（应该澄清...行是否已经存在于test_table1中，您正在尝试重新插入，还是您要更新test_table1中的值与table1相同？） - Twelfth

列出除主键外的所有列。但是会分配一个新的主键。 - Gordon Linoff

就像我在答案中所写的那样。 - Taleh Ibrahimli

@TalehIbrahimli 谢谢Taleh，你评论的最后一行已经解决了我的问题，所以我接受了你的答案。但是由于我对Postgresql的经验很少，没有想到序列会出现问题。 - Jerry Smith

Answer 1

只需要从查询的列中删除pk列即可

insert into test_table1 (col2,..., coln) select col2,...,coln from table1;

如果仍然失败，可能是因为您在主键列上没有序列。 在已经存在的主键列上创建序列。

create sequence test_table1_seq;
ALTER TABLE test_table1 
    ALTER COLUMN col1 SET DEFAULT nextval('test_table1_seq'::regclass);

将更新序列值更新为当前值

SELECT setval('test_table1_seq', (SELECT MAX(col1) FROM test_table1));

- Taleh Ibrahimli

---

嗨Taleh，谢谢回复，我已经编辑了问题 - pk列从未出现在查询中，但它确实存在于两个带有序列的表中。 - Jerry Smith

Answer 2

这篇文章 帮助我解决了我的问题，不确定出了什么问题：

---

How to fix PostgreSQL error "duplicate key violates unique constraint"

If you get this message when trying to insert data into a PostgreSQL database:

ERROR: duplicate key violates unique constraint

That likely means that the primary key sequence in the table you're working with has somehow become out of sync, likely because of a mass import process (or something along those lines). Call it a "bug by design", but it seems that you have to manually reset the a primary key index after restoring from a dump file. At any rate, to see if your values are out of sync, run these two commands:

SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_sequence');

If the first value is higher than the second value, your sequence is out of sync. Back up your PG database (just in case), then run thisL

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

That will set the sequence to the next available value that's higher than any existing primary key in the sequence.

- Jerry Smith

Answer 3

你更应该使用 UPDATE JOIN，例如：

UPDATE test_table1 AS v 
SET col1 = s.col1,
col2 = s.col2,
col3 = s.col3,
.....
colN = s.colN
FROM table1 AS s
WHERE v.id = s.id; 

- Rahul

Answer 4

你想要做的是一个 upsert 操作。

with upsert as (
    update test_table1 tt
    set col1 = t.col1,
        col2 = t.col2,
        col3 = t.col3
    from table1 t
    where t.id = tt.id 
    returning *
)
insert into test_table1(id, col1, col2, col3)
select id, col1,col2,col3
from table1
where not exists (select * from upsert)

- Richard Albright