title: How to retrieve foreign keys information from Postgresql
body: <p>I would like to replicate exactly the way MySql returns informations on FKs extracting them from Information_schema tables.</p>
  
  <p>I have these tables on MySql:</p>
  
  <pre class="lang-sql prettyprint-override"><code>create table test.subjects (
    ID_SUBJECT bigint NOT NULL AUTO_INCREMENT,
    FULL_NAME varchar(200) NOT NULL,
    PRIMARY KEY (ID_SUBJECT)
  );
  
  create table test.request_state (   
    ID_REQUEST_STATE char(3) NOT NULL,  
    DESCRIPTION varchar(80) NOT NULL,
    PRIMARY KEY (ID_REQUEST_STATE)
  );
  
  create table test.subject_profiles (    
    ID_SUBJECT_PROFILES bigint NOT NULL AUTO_INCREMENT,
    ID_SUBJECT bigint NOT NULL,
    ID_PROFILE bigint,    
    PRIMARY KEY (ID_SUBJECT_PROFILES)
  );
  
  CREATE UNIQUE INDEX subject_profiles_uq1
  ON test.subject_profiles (ID_SUBJECT, ID_PROFILE);
  
  ALTER TABLE test.subject_profiles add  
    CONSTRAINT subject_profiles_fk1
      foreign key (ID_SUBJECT) 
      REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
  
  create table test.demand (  
    ID_DEMAND bigint NOT NULL AUTO_INCREMENT,
    ID_SUBJECT bigint NOT NULL,
    DEMAND_STATE char(3) not null,
    ID_PROFILE bigint,                          
    PRIMARY KEY (ID_DEMAND)
  );
  
  ALTER TABLE test.demand add
    CONSTRAINT demand_fk1
      foreign key (ID_SUBJECT) 
      REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
  
  ALTER TABLE test.demand add
    CONSTRAINT demand_fk2 foreign key (DEMAND_STATE) REFERENCES test.request_state (ID_REQUEST_STATE);                                                       
  alter table test.demand
   add CONSTRAINT demand_fk3
      foreign key (ID_SUBJECT, ID_PROFILE) 
      REFERENCES test.subject_profiles (ID_SUBJECT, ID_PROFILE);
  </code></pre>
  
  <p>And their replica on Postgresql:</p>
  
  <pre class="lang-sql prettyprint-override"><code>create table test.subjects (
    ID_SUBJECT bigint NOT NULL,
    FULL_NAME varchar(200) NOT NULL,
    PRIMARY KEY (ID_SUBJECT)
  );
  
  create table test.request_state (   
    ID_REQUEST_STATE char(3) NOT NULL,  
    DESCRIPTION varchar(80) NOT NULL,
    PRIMARY KEY (ID_REQUEST_STATE)
  );
  
  create table test.subject_profiles (    
    ID_SUBJECT_PROFILES bigint NOT NULL,
    ID_SUBJECT bigint NOT NULL,
    ID_PROFILE bigint,    
    PRIMARY KEY (ID_SUBJECT_PROFILES)
  );
  
  CREATE UNIQUE INDEX subject_profiles_uq1
  ON test.subject_profiles (ID_SUBJECT, ID_PROFILE);
  
  ALTER TABLE test.subject_profiles add  
    CONSTRAINT subject_profiles_fk1
      foreign key (ID_SUBJECT) 
      REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
  
  create table test.demand (  
    ID_DEMAND bigint NOT NULL,
    ID_SUBJECT bigint NOT NULL,
    DEMAND_STATE char(3) not null,
    ID_PROFILE  bigint,
     PRIMARY KEY (ID_DEMAND)
  );
  
  ALTER TABLE test.demand add
    CONSTRAINT demand_fk1
      foreign key (ID_SUBJECT) 
      REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
  
  ALTER TABLE test.demand add
    CONSTRAINT demand_fk2
      foreign key (DEMAND_STATE) REFERENCES test.request_state (ID_REQUEST_STATE);                                                       
  alter table test.demand
   add CONSTRAINT demand_fk3
      foreign key (ID_SUBJECT, ID_PROFILE) 
      REFERENCES test.subject_profiles (ID_SUBJECT, ID_PROFILE);
  </code></pre>
  
  <p>Now, this query on MySql:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
    CONCAT(table_name) AS table_name, CONCAT(column_name) AS column_name,
    CONCAT(referenced_table_name) AS referenced_table_name,
    CONCAT(referenced_column_name) AS referenced_column_name
  FROM 
    INFORMATION_SCHEMA.key_column_usage 
  WHERE 
    referenced_table_schema = 'subjects_data'
    and referenced_table_name IS NOT NULL 
    and table_name = 'demand'
  ORDER BY table_name, column_name
  </code></pre>
  
  <p>Returns: </p>
  
  <pre><code>table_name  column_name  referenced_table_name referenced_column_name                              
  ---------------------------------------------------------------------
  demand      DEMAND_STATE request_state         ID_REQUEST_STATE                                    
  demand      ID_PROFILE   subject_profiles      ID_PROFILE                                          
  demand      ID_SUBJECT   subjects              ID_SUBJECT                                          
  demand      ID_SUBJECT   subject_profiles      ID_SUBJECT
  </code></pre>
  
  <p>While this my best Postgres "replica":</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT  
    tc.table_name,
    kcu.column_name,
    ccu.table_name as references_table,
    ccu.column_name as references_field
  FROM 
    information_schema.table_constraints tc
    LEFT JOIN information_schema.key_column_usage kcu
    ON tc.constraint_catalog = kcu.constraint_catalog
    AND tc.constraint_schema = kcu.constraint_schema
    AND tc.constraint_name = kcu.constraint_name
    LEFT JOIN information_schema.referential_constraints rc
    ON tc.constraint_catalog = rc.constraint_catalog
    AND tc.constraint_schema = rc.constraint_schema
    AND tc.constraint_name = rc.constraint_name
    LEFT JOIN information_schema.constraint_column_usage ccu
    ON rc.unique_constraint_catalog = ccu.constraint_catalog
    AND rc.unique_constraint_schema = ccu.constraint_schema
    AND rc.unique_constraint_name = ccu.constraint_name
  WHERE 
    tc.constraint_catalog = 'subjects_db'
    and tc.constraint_schema = 'test' 
    and tc.table_name = 'demand'
    and tc.constraint_type = 'FOREIGN KEY'
  </code></pre>
  
  <p>Returns:</p>
  
  <pre><code>table_name  column_name  referenced_table_name referenced_column_name
  ---------------------------------------------------------------------
  demand      id_subject   subjects              id_subject            
  demand      demand_state request_state         id_request_state      
  demand      id_subject   &lt;null&gt;                &lt;null&gt;                
  demand      id_profile   &lt;null&gt;                &lt;null&gt;                
  </code></pre>
  
  <p>So, finally, in which way I should rewrite my Postgres query in order to retrieve all the informations about referenced tables and columns? </p>
  
  <p>I believe there is something I'm missing as there are some nulls in the Postgresql result set.</p>
  
  <p>TIA!</p>