john1717 john1717 - 1 year ago 73
SQL Question

postgresql - compare two tables grouped by a column

i don't know if my title for this question is correct,
but here is my question

I have two tables (TB1 and TB2) both have same columns, here is the structure..


then I want to compare all the values in column_name grouped by table_name and data_type

the returning table should at least look like this (with sample data)...

user_account | id | id | charvar
user_account | user_name | user_name | charvar
user_account | date_expire | - | charvar
user_account | - | gender | int
user_account | gender | - | charvar
employee | id | id | charvar
employee | full_name | full_name | charvar

sorry but I have not started any query, cause I really don't know where / what to start..
thanks in advance to all who will response :D

Answer Source

I think you want a full outer join:

select coalesce(tb1.table_name, tb2.table_name) as table_name,
       tb1.column_name, tb2.column_name,
       coalesce(tb1.data_type, tb2.data_type) as data_type
from tb1 full outer join
     on tb1.table_name = tb2.table_name and
        tb1.column_name = tb2.column_name and
        tb1.data_type = tb2.data_type;