Here is the 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.