1

I'm trying to get a list of foreign key constraints in the following form:

pk_table | pk_col | fk_table | fk_col

without using pg_get_constraintdef (which would force me to resort to string manipulation).

Is there any reason why the following query doesn't work?

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.tablename,
  fk.tablename
FROM pg_catalog.pg_constraint x 
INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND x.conrelid=(pk.tablename :: regclass :: oid)
INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
WHERE
  x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
  AND x.contype='f'

Error:

ERROR:  relation "sql_implementation_info" does not exist

My hypothesis was that PG is getting confused due to the presence of multiple namespaces, so I tried restricting the result-set to my known schemas, i.e. b2b, public, but this still doesn't work.

3
  • I installed psql the other day, so I tried your query and it works (0 rows returned of course, but no error). Tested on psql (9.6.4, server 9.6.3)
    – Lennart
    Aug 17 '17 at 9:09
  • Can you try it on a DB which has some tables and FK constraints? My gut-feeling is that the query is well formed, but some rows are causing the error. Aug 17 '17 at 9:11
  • I'll add some info in an answer, it will be to long for a comment
    – Lennart
    Aug 17 '17 at 9:14
1

The problem is that not all of "tables" presented in the pg_tables view have OID presented in the pg_class relation:

postgres=# select tablename::regclass from pg_catalog.pg_tables;
ERROR:  relation "sql_features" does not exist

You should to rewrite your query to more "low-level":

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.relname,
  fk.relname
FROM pg_catalog.pg_constraint x 
INNER JOIN pg_catalog.pg_class pk ON x.conrelid!=0 AND x.conrelid=pk.oid
INNER JOIN pg_catalog.pg_class fk ON x.confrelid!=0 AND x.confrelid=fk.oid
WHERE
  x.contype='f';

test

0

I queried an empty database and your query was fine:

test=> SELECT
test->   x.conname,
test->   x.contype,
test->   x.conrelid,
test->   x.confrelid,
test->   pk.tablename,
test->   fk.tablename
test-> FROM pg_catalog.pg_constraint x 
test-> INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND         x.conrelid=(pk.tablename :: regclass :: oid)
test-> INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
test-> WHERE
test->   x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
test->   AND x.contype='f';
 conname | contype | conrelid | confrelid | tablename | tablename 
---------+---------+----------+-----------+-----------+-----------
(0 rows)

However when I created two tables with a foreign key:

test=> create table parent (x int not null primary key);
CREATE TABLE
test=> create table child (y int not null primary key, x int not null    references parent(x));
CREATE TABLE

I got the following :

ERROR:  relation "sql_features" does not exist

At first I thought it was a problem with my user but I got the same error when sudoing to psql.

So I started investigating the catalog in postgres, and I guess that you should be fine with information from constraint_column_usage and information_schema.table_constraints

select * from information_schema.constraint_column_usage;
select * from information_schema.table_constraints;

I'm not sure exactly what your query is supposed to return, so I'll leave it there.

I don't know if there are any drawbacks using information_schema vs pg_catalog (I suppose some psql specific stuff is not in information_schema), but the information_schema looks pretty much like information_schemas in other DBMS:s. So if you know your way around the information_schema in psql, you should be able to use that knowledge in other DBMS.

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.