1

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

Tisha
  • 827
  • 3
  • 11
  • 34

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • and then inserting rows into the actual table - db2.employee from the foreign table - how to do that? – Tisha Nov 14 '16 at 15:07
  • `db2.employee` *is* the foreign table, no? If you want to insert into two tables use something like `WITH x AS (INSERT ... RETURNING ...) INSERT ... SELECT ... FROM x`. – Laurenz Albe Nov 14 '16 at 16:36
  • db2.employee is an actual table - which exists in db2. It has the same schema as db1.employee – Tisha Nov 14 '16 at 17:00
  • Do I need a foreign table even though I have an exact replica of that table in the db2? – Tisha Nov 15 '16 at 05:31
  • I have updated my question with Edit 1 to explain further what I am trying to do – Tisha Nov 15 '16 at 05:53
  • I expounded a bit, does it make more sense now? – Laurenz Albe Nov 15 '16 at 08:25
  • Hmmm may be I am not understanding but I cannot figure out what the query will be when I have to read the foreign table and insert data in the actual employee table in db2? Which database should this query be run and what will it look like? – Tisha Nov 15 '16 at 08:50
  • I do not understand at all. Maybe you should rewrite the question and state precisely which table is where and where you want the query to run. – Laurenz Albe Nov 15 '16 at 09:16