postgresql - SQL - fixing Join issue

Question

I have a query like below.

select   c.relname as Table,ex.location as path,
case when ex.fmttype='t' then 'text' when ex.fmttype='c' then 'csv'
else 'Unknown' end as format, case when ex.writable='0' then 'Yes' else 'No' end as is_readonly
from pg_exttable ex 
join pg_class c on ex.reloid=c.oid;

On the table pg_exttable I have a column called reloid. I want to add a new column in this query from another table. On the other table the column oid can be a join condition.

So I tried this query.

select  ns.nspname as schema,c.relname as Table,ex.location as path,
case when ex.fmttype='t' then 'text' when ex.fmttype='c' then 'csv'
else 'Unknown' end as format, case when ex.writable='0' then 'Yes' else 'No' end as is_readonly
from pg_exttable ex 
join pg_class c on ex.reloid=c.oid 
join pg_catalog.pg_namespace ns on ex.reloid=ns.oid  ;

But its giving 0 rows. I dont know where is the issue with this join.

Answer 1

Seems there are no rows to join to the existing result in the last table. You can use left outer join to fill the missing values with null:

select  
    ns.nspname as schema,
    c.relname as Table,
    ex.location as path,
    case 
        when ex.fmttype = 't' then 'text' 
        when ex.fmttype = 'c' then 'csv'
        else 'Unknown' 
    end as format, 
    case 
        when ex.writable = '0' then 'Yes' 
        else 'No' 
    end as is_readonly
from pg_exttable ex 
join pg_class c on ex.reloid = c.oid 
left outer join pg_catalog.pg_namespace ns on ex.reloid = ns.oid;