I want to be able to list out the columns in a child table and see which ones are inherited from the parent table and which ones are defined on the child table.

SELECT column_name, inheritied
FROM column_view
WHERE table_name = 'a_child_table'
| column_name       |  inherited   |
| foobar            |      f       |
| whizbang          |      t       |

Is this possible?


This query uses pg_inherits, which tracks table inheritance:

SELECT a.attname,
       EXISTS (
          SELECT 1
          FROM pg_inherits AS i
             JOIN pg_attribute AS a2
                ON i.inhparent = a2.attrelid
             WHERE i.inhrelid = a.attrelid
               AND a.attname = a2.attname
       ) AS inherited
FROM pg_attribute AS a
WHERE a.attrelid = 'tablename'::regclass
ORDER BY a.attnum;
| improve this answer | |
  • Thanks! you're missing a comma after a.attname but otherwise is what I was looking for – Eric Satterwhite May 20 '19 at 16:29
  • Thanks for testing, fixed. – Laurenz Albe May 20 '19 at 19:00

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.