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屋！