postgresql: INSERT INTO ... (SELECT * ...)

Quick Fix: Use the PostgreSQL dblink module to connect to the remote database and fetch data. Insert selected columns into the local table using a WHERE clause to filter results.

The Problem:

You are working with two PostgreSQL databases, tblA and tblB, located on separate servers. You want to insert data from tblB into tblA based on a condition. However, you are unsure if PostgreSQL provides a built-in utility or functionality that allows you to utilize the results of a SELECT query (stored in a PGresult struct) directly in an INSERT statement.

The Solutions:

Solution 1: Using `dblink` to Connect Remote Database and Fetch Result

To insert data from a table in a remote database into a table in a local database, you can use the `dblink` extension in PostgreSQL. Here’s how you can do it:

Establish a Connection to the Remote Database:

Use the `dblink` extension to establish a connection to the remote database. You can do this using the following command:

CREATE DATABASE CONNECTION dblink_connection
HOST remote_host
PORT remote_port
USER remote_user
PASSWORD remote_password
DBNAME remote_database;
```</li>

<li><b>Create Tables in Local and Remote Databases:</b>
<br>Create tables in both the local and remote databases with the same structure and column names. Ensure that the data types in both tables are compatible.</li>

<li><b>Insert Data from Remote Table to Local Table:</b>
<br>Use the following query to insert data from the remote table into the local table:

INSERT INTO local_table
SELECT *
FROM dblink(‘dblink_connection’, ‘SELECT * FROM remote_table’);


<br>Replace <code>local_table</code> with the name of the local table, <code>remote_table</code> with the name of the remote table, and <code>dblink_connection</code> with the name of the database connection you created earlier.</li>

<li><b>Verify Inserted Data:</b>
<br>To verify that the data was successfully inserted, you can query the local table and check the results.</li></ol>

<p>Note that the `dblink` extension needs to be installed and configured on both the local and remote databases for this to work.</p>

<p>Additionally, you can also use the `PREPARE` and `EXECUTE` statements to create and execute a prepared statement for the `INSERT` query, which can improve performance for repeated inserts.</p>

Solution 2: Using DB Link

To insert data from a table in a different database server into a table in the current database, you can use the DB Link feature in PostgreSQL. Here’s an example:

CREATE DB LINK dblink_to_dbtest
HOST &#39;<span class="ql-cursor">[db_server_host]</span>&#39;
USER &#39;<span class="ql-cursor">[db_user]</span>&#39;
PASSWORD &#39;<span class="ql-cursor">[db_password]</span>&#39;';

Create a DB Link named dblink_to_dbtest that connects to the remote database server and provides the necessary credentials.

Now, you can perform an insert query using the DB Link:

INSERT INTO tblA (time)
(SELECT time FROM 
    dblink(&#39;dbname=dbtest&#39;, &#39;SELECT time FROM tblB&#39;) AS t(time integer) 
    WHERE time > 1000
);

This query retrieves data from the tblB table in the remote database (accessible via the dblink_to_dbtest DB Link) and inserts only the time column into the tblA table in the current database.

Note: Make sure you have the necessary permissions and privileges to access the remote database and tables.

Solution 4: Using [dblink][1] to Create a View

To insert data from one database server to another, you can use the **[dblink][1]** extension in PostgreSQL. This extension allows you to create a view that is resolved in another database. The database may be on another server.

For example, let’s say you have two database servers: server1 and server2. On server1, you have a table called tblA. On server2, you have a table called tblB.

To insert data from tblB on server2 into tblA on server1, you can create a view on server1 that references tblB on server2.

Here’s how you would do it:

On server2, create a foreign data wrapper (FDW) for server1.

On server2, create a server for server1 using the FDW.
On server1, create a foreign table that references tblB on server2.
On server1, create a view that references the foreign table.

On server1, insert data into the view.

Once you have created the view, you can insert data into it using the following statement:

INSERT INTO view_name (column1, column2, ...)
SELECT column1, column2, ...
FROM foreign_table;

Note: The actual syntax for creating the FDW, server, foreign table, and view will vary depending on your specific database setup.

Solution 5: Syntactic Sugar for Transfers

You can use standard PL/pgSQL to move the data between tables in different databases:

CREATE OR REPLACE FUNCTION transfer_data ( 
  source_db   TEXT, 
  source_user TEXT, 
  source_pass TEXT, 
  source_host TEXT, 
  source_port INT, 
  source_database TEXT, 
  source_table TEXT, 
  dest_db     TEXT, 
  dest_user   TEXT, 
  dest_pass   TEXT, 
  dest_host   TEXT, 
  dest_port   INT, 
  dest_database TEXT, 
  dest_table  TEXT)
RETURNS TEXT AS
$BODY$
DECLARE
  -- DEFINE THE SOURCE CONNECTION DETAILS AS A STRING
  source_conn_string TEXT;
  -- DEFINE THE DESTINATION CONNECTION DETAILS AS A STRING
  dest_conn_string TEXT;
  -- THE DATABASE CONNECTION HANDLER
  pconn CONNECTION;
BEGIN
  -- CREATE THE SOURCE CONNECTION STRING
  source_conn_string := 'host=' || source_host ||
                       ' port=' || source_port ||
                       ' dbname=' || source_database ||
                       ' user=' || source_user ||
                       ' password=' || source_pass;
  
  -- CREATE THE DESTINATION CONNECTION STRING
  dest_conn_string := 'host=' || dest_host ||
                      ' port=' || dest_port ||
                      ' dbname=' || dest_database ||
                      ' user=' || dest_user ||
                      ' password=' || dest_pass;
                      
  -- CONNECT TO THE SOURCE DATABASE 
  pconn := connect ( source_conn_string );
  IF NOT FOUND THEN
    RETURN 'Unable to connect to source database: ' || source_conn_string;
  END IF;
  
  -- COPY THE DATA FROM THE SOURCE DATABASE TO THE DESTINATION DATABASE
  EXECUTE 'COPY (SELECT * FROM ' || quote_literal(source_table) || ') TO ' ||
           quote_literal(dest_conn_string) || ' WITH DELIMITER ,';  
  
  -- CLOSE THE CONNECTION TO THE SOURCE DATABASE
  disconnect ( pconn );
  RETURN 'Data transfer completed.';    
END;
$BODY$
LANGUAGE plpgsql;

postgresql: INSERT INTO … (SELECT * …) – Sql