comparing output of internal pg tables of referenced tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey,
I tried to get a list of all tables that has a reference to my_table. I used two different queries : 

1)select R.*
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
  AND U.TABLE_NAME = 'my_table'

2)select conname,  (select r.relname from pg_class r where r.oid = c.confrelid) as orig_table,    (select array_agg(attname) from pg_attribute    where attrelid = c.confrelid and ARRAY[attnum] <@ c.conkey) as orig_cols,  (select r.relname from pg_class r where r.oid = c.conrelid) as foreign_table,      (select array_agg(attname) from pg_attribute    where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as foreign_cols from pg_constraint c   where c.confrelid = (select oid from pg_class where relname = 'my_table') and c.contype='f'

On the second output in the orig_cols I got a few weird outputs like : {........pg.dropped.5........} or even a columns that doesnt have a unique index (just a random column from the orig_table).

tried to vacuum the table but still didnt help. The db is at version 9, but I tried to upgrade it to 10/11/12 and in all versions it stayed the same.

;

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux