CODEWITHSUNDEEP
postgresqlforeign-keys
Groovieman
Groovieman

Reputation: 33

Several attempts to compute a pk/fk relationship of tables in postgres using the information-schema show weired constraint_name containing a $1

I am trying to anaylse a postgres database, finding all pk/fk relationsships between two tables. I found a couple solutions here on stackoverflow.

All suggested solution here on stackoverflow do return correct pk/fk-relationsships, but unfortunately in addition they also add weird and non existing relationships in their select-result. All of the unwanted non-relationships have a

These weird not-relation show a '$1' value in the information_schema.table_constraints.constraint_name column.

The adjacent SQL fixes the problem with the exclusive condition tc.constraint_name <> '$1' inside the where-clause.

SELECT
  tc.constraint_name, tc.table_name, kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM
  information_schema.table_constraints AS tc

  JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name

  JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name

WHERE
  tc.constraint_type = 'FOREIGN KEY' AND
  tc.constraint_name <> '$1'

Can somebody tell me, what is the magic of this $1 constraint, that may indicate commonly shared fk-indexes.

Upvotes: 0

Views: 35

Answers (0)

Related Questions