Início / dba / Perguntas / 183708
Accepted
Saurabh Nanda
Asked: 2017-08-18 00:56:35 +0800 CST

Não é possível ingressar nas colunas conrelid e confrelid em pg_constraint

  • 772

Estou tentando obter uma lista de restrições de chave estrangeira no seguinte formulário:

pk_table | pk_col | fk_table | fk_col

sem usar pg_get_constraintdef(o que me forçaria a recorrer à manipulação de strings).

Existe alguma razão pela qual a consulta a seguir não funciona?

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'

Erro:

ERROR:  relation "sql_implementation_info" does not exist

Minha hipótese era que o PG está ficando confuso devido à presença de vários namespaces, então tentei restringir o conjunto de resultados aos meus esquemas conhecidos, ou seja b2b, public, , mas isso ainda não funciona.

postgresql information-schema

2 respostas

  1. Best Answer

    O problema é que nem todas as "tabelas" apresentadas na pg_tablesview foram OIDapresentadas na pg_classrelação:

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

    Você deve reescrever sua consulta para mais "baixo nível":

    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

    • 1

  2. Eu consultei um banco de dados vazio e sua consulta estava bem:

    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)

    No entanto, quando criei duas tabelas com uma chave estrangeira:

    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

    Eu obtive o seguinte:

    ERROR:  relation "sql_features" does not exist

    No começo eu pensei que era um problema com o meu usuário, mas recebi o mesmo erro ao sudo para o psql.

    Então comecei a investigar o catálogo no postgres, e acho que você deve estar bem com informações de constraint_column_usage e information_schema.table_constraints

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

    Não tenho certeza exatamente o que sua consulta deve retornar, então vou deixá-la lá.

    Não sei se há alguma desvantagem usando information_schema vs pg_catalog (suponho que algumas coisas específicas do psql não estejam em information_schema), mas o information_schema se parece muito com information_schemas em outros DBMS:s. Portanto, se você conhece o information_schema no psql, deve ser capaz de usar esse conhecimento em outros DBMS.

    • 0

relate perguntas