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

Question

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

Laurenz Albe · Accepted Answer · 2016-11-15 08:24:52Z

4

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.

edited Nov 15, 2016 at 8:24

answered Nov 14, 2016 at 14:04

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Tisha Over a year ago

and then inserting rows into the actual table - db2.employee from the foreign table - how to do that?

Laurenz Albe Over a year ago

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.

Tisha Over a year ago

db2.employee is an actual table - which exists in db2. It has the same schema as db1.employee

Tisha Over a year ago

Do I need a foreign table even though I have an exact replica of that table in the db2?

Tisha Over a year ago

I have updated my question with Edit 1 to explain further what I am trying to do

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related