How would I formulate an insert query into another database using postgres_fdw?

Here is the query that I want to run - Let's the current database that I am running this query in is db1 and the database in which I want to insert records is db2.

  insert into db2.employee(id, name, group) values(id, <db1.employee.name>, <db1.employee.group>) where id in (select id from archived limit 2);

As you can see, the values have to be selected from employee table of db1 (current database) and then inserted into employee table of db2.

I know the query is wrong - but I have just written to explain what I am trying to do.

How would I formulate a query using postgres_fdw?

I have tried doing something the other way round using this query

INSERT INTO  employee select * from 
dblink('host=10.1.1.1
 user=user
 password=password
 dbname=mydb', 'select * from employee') tt(
       id int,
  name character varying,
 );

Edit: PLEASE Note that I want to do a remote insert and a local select. It seems like I am able to achieve this using the query above when I run the query in db2 as my local database instead of db1

Solution

You cannot access tables in a different database directly like you can do in MySQL (a MySQL database corresponds to a PostgreSQL schema).

So maybe you can achieve what you want either by using different schemas in a single database.

If you really need to update a table in a different database, you'll have to use the postgres_fdw foreign data wrapper to access the foreign table.

You'll have to define a foreign table – let's call it foreign_employee – in database db1 that points to a table in db2.

Then you could do it like that:

INSERT INTO foreign_employee
SELECT e.*
FROM employee e
     JOIN archived a USING id
LIMIT 2;

Note that the LIMIT is a bit strange here, since there is no implicit order in a query result unless you force one with ORDER BY.