SQL postgresql: INSERT INTO ... (SELECT * ...)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6083132/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql: INSERT INTO ... (SELECT * ...)
提问by Mayank
I'm not sure if its standard SQL:
我不确定它的标准 SQL 是否：
INSERT INTO tblA
(SELECT id, time
FROM tblB
WHERE time > 1000)
What I'm looking for is: what if tblA and tblB are in different DB Servers.
我正在寻找的是：如果 tblA 和 tblB 在不同的 DB Servers 中会怎样。
Does PostgreSql gives any utility or has any functionality that will help to use INSERT query with PGresult struct
PostgreSql 是否提供任何实用程序或具有任何有助于使用的功能 INSERT query with PGresult struct
I mean SELECT id, time FROM tblB ...will return a PGresult*on using PQexec. Is it possible to use this struct in another PQexecto execute an INSERT command.
我的意思SELECT id, time FROM tblB ...是PGresult*在使用时会返回一个PQexec。是否可以在另一个结构中使用此结构PQexec来执行 INSERT 命令。
EDIT:
If not possible then I would go for extracting the values from PQresult* and create a multiple INSERT statement syntax like:
编辑：
如果不可能，那么我会从 PQresult* 中提取值并创建一个多 INSERT 语句语法，如：
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!! :(
是否可以从中创建一个准备好的声明！！:(
回答by Grzegorz Szpetkowski
As Henrik wrote you can use dblink to connect remote database and fetch result. For example:
正如 Henrik 所写，您可以使用 dblink 连接远程数据库并获取结果。例如：
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 has recordpseudo-type (only for function's argument or result type), which allows you query data from another (unknown) table.
PostgreSQL 具有记录伪类型（仅用于函数的参数或结果类型），它允许您从另一个（未知）表中查询数据。
Edit:
编辑：
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 > ;
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).
我刚刚看到您修改后的问题（关闭重复，或与此非常相似）。
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 insertas above):
如果我的理解是正确的（postgres 有 tbla 并且 dbtest 有 tblb 并且你想要远程插入本地 select，而不是远程选择本地插入如上所述）：
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;'
);
I don't like that nested dblink, but AFAIK I can't reference to tblB in dblink_execbody. Use LIMIT to specify top 20 rows, but I think you need to sort them using ORDER BY clause first.
我不喜欢那个嵌套的 dblink，但我不能在dblink_exec主体中引用tblB。使用 LIMIT 指定前 20 行，但我认为您需要先使用 ORDER BY 子句对它们进行排序。
回答by Piotr Olaszewski
If you want insert into specify column:
如果要插入指定列：
INSERT INTO table (time)
(SELECT time FROM
dblink('dbname=dbtest', 'SELECT time FROM tblB') AS t(time integer)
WHERE time > 1000
);
回答by Hendrik Brummermann
回答by Sombriks
This notation (first seen here) looks useful too:
这个符号（第一次在这里看到）看起来也很有用：
insert into postagem (
resumopostagem,
textopostagem,
dtliberacaopostagem,
idmediaimgpostagem,
idcatolico,
idminisermao,
idtipopostagem
) select
resumominisermao,
textominisermao,
diaminisermao,
idmediaimgminisermao,
idcatolico ,
idminisermao,
1
from
minisermao
回答by Nitin Nain
Here's an alternate solution, without using dblink.
这是一个替代解决方案，不使用dblink.
Suppose B represents the source database and A represents the target database: Then,
假设 B 代表源数据库，A 代表目标数据库： 那么，
Copy table from source DB to target DB:
pg_dump -t <source_table> <source_db> | psql <target_db>Open psql prompt, connect to target_db, and use a simple
insert:psql # \c <target_db>; # INSERT INTO <target_table>(id, x, y) SELECT id, x, y FROM <source_table>;At the end, delete the copy of source_tablethat you created in target_table.
# DROP TABLE <source_table>;
将表从源数据库复制到目标数据库：
pg_dump -t <source_table> <source_db> | psql <target_db>打开 psql 提示符，连接到target_db，并使用一个简单的
insert：psql # \c <target_db>; # INSERT INTO <target_table>(id, x, y) SELECT id, x, y FROM <source_table>;最后，删除您在target_table 中创建的source_table副本。
# DROP TABLE <source_table>;
回答by mahesh ingale
insert into TABLENAMEA (A,B,C,D)
select A::integer,B,C,D from TABLENAMEB
