postgresql:插入...(选择* ...) [英] 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 是否提供任何实用程序或任何功能有助于将 INSERT 查询与 PGresult 结构一起使用

Does PostgreSql gives any utility or has any functionality that will help to use INSERT query with PGresult struct

我的意思是 SELECT id, time FROM 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 有 record 伪类型(仅适用于函数参数或结果类型)，它允许您从另一个(未知)表中查询数据.

PostgreSQL has record pseudo-type (only for function's argument or result type), which allows you query data from another (unknown) table.

如果你愿意，你可以将它作为准备好的语句，它也可以工作:

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，但我无法在 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:插入...(选择* ...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！