How to Insert Into Select in PostgreSQL

1. Insert Data From a Database Hosted on a Remote Server to a Database in the Local Machine
2. Use dblink to Insert Into Select in PostgreSQL

This tutorial will learn how to insert data from a database hosted on a remote server to a database in our local machines. We will use the remote service provider Heroku PostgreSQL, which provides a free plan for testing purposes.

Insert Data From a Database Hosted on a Remote Server to a Database in the Local Machine

Create a new app and add a PostgreSQL data store. The credentials to connect to the remote server can be accessed by clicking the Settings link.

To connect to the remote database, use the following command and ensure that you are not connected to a corporate network to avoid timeout errors caused by communication failure.

>psql -h ec2-52-49-56-163.eu-west-1.compute.amazonaws.com -d dagbp3p323638g -U cvkmaeedgzlxdr

Output:

dagbp3p323638g=>

Create a table named remote_employee that will hold records containing information of employees such as id, first name, last name, and email.

dagbp3p323638g=> create table remote_employee(id SERIAL NOT NULL UNIQUE,first_name varchar(50),last_name varchar(50),email varchar(50),PRIMARY KEY(id));
CREATE TABLE

Add three records to the remote_employee table to ensure we have data to fetch from the remote server to the local server.

dagbp3p323638g=> insert into remote_employee(first_name,last_name,email)values ('john','doe','john@gmail.com');
INSERT 0 1

dagbp3p323638g=> insert into remote_employee(first_name,last_name,email)values ('peter','parker','peter@gmail.com');
INSERT 0 1

dagbp3p323638g=> insert into remote_employee(first_name,last_name,email)values ('mary','public','mary@gmail.com');
INSERT 0 1

The following query verifies that our three records were inserted into the database.

dagbp3p323638g=> select * from remote_employee;
 id | first_name | last_name |      email
----+------------+-----------+-----------------
  1 | john       | doe       | john@gmail.com
  2 | peter      | parker    | peter@gmail.com
  3 | mary       | public    | mary@gmail.com
(3 rows)

You must connect to the local server and create a database named local_database which will contain our local entity to be filled with data from the remote entity.

>psql -U postgres

postgres=# create database local_database;
CREATE DATABASE

postgres=# \c local_database;
You are now connected to database "local_database" as user "postgres".

Create a table named local_employee with the same columns as the remote_employee as they contain the same data type.

local_database=# create table local_employee(id SERIAL NOT NULL UNIQUE,first_name varchar(50),last_name varchar(50),email varchar(50),PRIMARY KEY(id));
CREATE TABLE

Use dblink to Insert Into Select in PostgreSQL

The dblink is an extension that allows us to query data from other databases, and we use the following command to create an extension.

local_database=# create extension dblink;

Before fetching data from the remote server, we can test the connection using dblink_connect, as shown below. If the query returns OK, this shows that our remote database has connected successfully, and we can now execute insert, update, and delete queries.

Note the connection name temp_conn, which we will use in our insert query.

local_database=# SELECT dblink_connect('temp_conn', 'dbname=dagbp3p323638g port=5432 host=ec2-52-49-56-163.eu-west-1.compute.amazonaws.com user=cvkmaeedgzlxdr password=336ea6e67129e8f082140f1b60954dafa33940f17b02e1f580ea45f10401f85e');
 dblink_connect
----------------
 OK
(1 row)

The query to insert data to our local_employee table is formed by an insert statement and a select statement to our remote server.

The select statement is executed using dblink by specifying the connection name temp_conn and an SQL string to fetch the data.

local_database=# INSERT INTO local_employee
SELECT id,first_name,last_name,email FROM dblink('temp_conn','SELECT id, first_name, last_name, email FROM remote_employee') AS temp_employee(id integer,first_name varchar(50),last_name varchar(50),email varchar(50));
INSERT 0 3

All the records in the remote_employee are returned as a temporary table temp_employee and successfully inserted into the local table local_employee by leveraging the dblink connection.

local_database=# select * from local_employee;

Output:

id | first_name | last_name |      email
----+------------+-----------+-----------------
  1 | john       | doe       | john@gmail.com
  2 | peter      | parker    | peter@gmail.com
  3 | mary       | public    | mary@gmail.com
(3 rows)