How to insert values to one table from the table stored in another database in postgresql?

Question

Here is the structure of table: Structure of table

`* Table 1 of database A contains mobile number and userId and both field cannot be blank

I want to fetch data from table 1 of database A and insert that with some other data in the table 1 of database B, Following are some clause that I have to follow during insertion and selection of data:

1. Check whether userid exists of a particular phone number from table 1 of Database A If user id exists the Insert the row to Table B.1 of database B without phone number

2. If userId does not exists in the table A.1 of database A then Insert the row into Table B.1 of database B with null userId and with phone number (i.e. row 3rd of Table B.1)

So far what I have tried is:

WITH useridTab AS 
(SELECT *
 FROM dblink('postgres://postgres:password@localhost/databaseA?sslmode=disable', 
 E'SELECT mobilenum,game_user_id FROM table1 where mobilenum=\'1234\' UNION ALL
 SELECT \'1234\', null 
limit 1') as data(mobilenum text,userid text))
insert into table1 (userid,phonenumber,email,address) select userid ,case when userid is null then mobilenum else 'No One' end,'[email protected]','AbcAddress' from useridtab;

I used DB link to fetch data from table stored in some other database, created CTE of it then used that to insert values to table1 of database B. Just wanted to whether this approach is correct or some more optimization can be done.

Answer 1

Using a Foreign Data Wrapper is generally preferable to using dblink. Once you set up the link and import the table definition, then you can write the query just as if the table were local.

Your English description of the problem doesn't make any sense to me, so I can't tell you if the specific query you wrote does what you want.