2

I have two databases. In each database I have an employee table which differ from each other in the number of columns and column names.

Here is what the table looks in the first database:

 Emp_ID    Name        Father_Name      Family_Name     
 1         Max         Stjepan          Neg               
 2         John        Chris            James           

Here is what the second table looks like which will be in the second database:

 ID        Name        Father_Name      Family_Name   Blood_Type
 1         Max         Stjepan          Neg           ARH+
 2         John        Chris            James         ORH-

Now when creating the foreign table in Second Database I want to store the Emp_ID values from First Database with column name ID and also I want my foreign table to include the blood type from the table which is in the current database Second Database.

Shortly, how can I create a foreign table with different column names than the original table in remote database? It should also include the columns from first table which is in the current database.

2

You can name the columns of your foreign table whatever you want. For example, after an IMPORT FOREIGN SCHEMA ... you can do

ALTER FOREIGN TABLE myschema.mytable RENAME a_column TO another_column;

(For CREATE FOREIGN TABLE, you have to specify the different column name in the OPTIONS clause to the column: CREATE FOREIGN TABLE foreign.employee (emp_id integer OPTIONS (column_name 'id'), ...)

What you cannot do is mixing columns from local and foreign tables. To achieve something similar, you can create a view:

CREATE VIEW unified_employee AS
SELECT local.id AS emp_id,
       local.name,
       ...,
       foreign.blood_type
  FROM local_schema.employee AS local
  JOIN foreign_schema.employee AS foreign ON local.id = foreign.emp_id;

I swapped the tables in this example on purpose. Your example tables don't differ enough, so a simple view on the local employee table, where the id column is named emp_id would do the trick, without ever touching the other database.

  • Thank you. First can you please tell me how could I specify the column name during generating the foreign table in the options. and second yes I had the idea to create a view for mixing the columns from both local and foreign databases. but my application interacts with this foreign table and if I do create a view what would be the case of DML statements that will come from application side. - @dezso – Sayad Xiarkakh Mar 6 '17 at 11:15
  • 1
    @Sayad: you can make the view updateable. As you are using a join in the view, you will need an instea of trigger – a_horse_with_no_name Mar 6 '17 at 11:22
  • @a_horse_with_no_name: could you please send me a link on which the use of instead of trigger for an unpalatable view is explained. I have searched but got nothing satisfactory. Thank you. – Sayad Xiarkakh Mar 7 '17 at 6:14
  • @Sayad: the link in my comment (to the manual) has a complete example – a_horse_with_no_name Mar 7 '17 at 6:17
  • @a_horse_with_no_name: I am sorry, I did not notice that, Thank you. – Sayad Xiarkakh Mar 7 '17 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.