PostgreSQL:INSERT INTO ...(选择* ...) [英] postgresql: INSERT INTO ... (SELECT * ...)
问题描述
我不确定它的标准SQL:
I'm not sure if its standard SQL:
INSERT INTO tblA
(SELECT id, time
FROM tblB
WHERE time > 1000)
我在寻找什么是:如果tblA和tblB位于不同的数据库服务器中怎么办。
PostgreSql提供任何实用程序或任何功能来帮助使用使用PGresult结构的INSERT查询
Does PostgreSql gives any utility or has any functionality that will help to use INSERT query with PGresult struct
我的意思是 SELECT id,时间从tblB ...
使用 PQexec
将返回 PGresult *
。是否可以在另一个 PQexec
中使用此结构来执行INSERT命令。
I mean SELECT id, time FROM tblB ...
will return a PGresult*
on using PQexec
. Is it possible to use this struct in another PQexec
to execute an INSERT command.
编辑:
如果不可能的话,我将从PQresult *中提取值并创建多个INSERT语句语法,例如:
If not possible then I would go for extracting the values from PQresult* and create a multiple INSERT statement syntax like:
INSERT INTO films (code, title, did, date_prod, kind) VALUES
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
是否可以由此创建准备好的语句! :(
Is it possible to create a prepared statement out of this!! :(
推荐答案
如Henrik所写,您可以使用dblink连接远程数据库并获取结果。例如:
As Henrik wrote you can use dblink to connect remote database and fetch result. For example:
psql dbtest
CREATE TABLE tblB (id serial, time integer);
INSERT INTO tblB (time) VALUES (5000), (2000);
psql postgres
CREATE TABLE tblA (id serial, time integer);
INSERT INTO tblA
SELECT id, time
FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
AS t(id integer, time integer)
WHERE time > 1000;
TABLE tblA;
id | time
----+------
1 | 5000
2 | 2000
(2 rows)
PostgreSQL具有记录伪类型(仅用于函数的参数或结果类型),它使您可以从另一个(未知)表中查询数据。
PostgreSQL has record pseudo-type (only for function's argument or result type), which allows you query data from another (unknown) table.
编辑:
您可以将其作为准备好的政治家如果需要,则t也可以:
You can make it as prepared statement if you want and it works as well:
PREPARE migrate_data (integer) AS
INSERT INTO tblA
SELECT id, time
FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
AS t(id integer, time integer)
WHERE time > $1;
EXECUTE migrate_data(1000);
-- DEALLOCATE migrate_data;
编辑(是的,另一个):
Edit (yeah, another):
我刚刚看到了您的修订后的问题(重复出现,或者与此非常相似) )。
I just saw your revised question (closed as duplicate, or just very similar to this).
如果我的理解是正确的(postgres使用tbla,dbtest使用tblb,并且您要使用本地选择进行远程插入,而不是带有本地插入的远程选择,如上所述):
If my understanding is correct (postgres has tbla and dbtest has tblb and you want remote insert with local select, not remote select with local insert as above):
psql dbtest
SELECT dblink_exec
(
'dbname=postgres',
'INSERT INTO tbla
SELECT id, time
FROM dblink
(
''dbname=dbtest'',
''SELECT id, time FROM tblb''
)
AS t(id integer, time integer)
WHERE time > 1000;'
);
我不喜欢嵌套的dblink,但是AFAIK我不能在 dblink_exec 正文。使用LIMIT指定前20行,但我认为您需要先使用ORDER BY子句对它们进行排序。
I don't like that nested dblink, but AFAIK I can't reference to tblB in dblink_exec body. Use LIMIT to specify top 20 rows, but I think you need to sort them using ORDER BY clause first.
这篇关于PostgreSQL:INSERT INTO ...(选择* ...)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!