PostgreSQL no tables listed under the public schema

Edit Subject

Hello Olivier,
we still have no luck reproducing your problem. Would you mind and try something for us, please?
Can your create a table manually in DQ Analyzer SQL Viewer in public schema and check whether you are able or not to see the table created there? You might have to refresh the Database tree or disconnect and reconnect to the database.

Martin,

I tried creating a table from the "Open in SQL Editor" menu. Table is created successfully, I can see it from pgAdmin III but not from Ataccama. I tried refreshing the tree and disconnecting/reconnecting but it didn't help.

SQL Editor can use this connection to create the table, but tables, apart from system tables, are not displayed in the tree.

Best regards,

Olivier.

Hello Olivier,
it is still strange for us, we are not able to reproduce it. We have found a statement done by the JDBC driver to list the tables, can you try and run it in DQ Analyzer like you have created the table?

Hopefully it will end up with some kind of informative exception, will list all the tables or anything that provides us more info regarding the issue.

The statement lists all indices, tables and views together with schema names. Try and look for the tables in public schema or send us anything strange like an exception in the output.

The query:
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'
WHEN true THEN
CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind
WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL
END
WHEN n.nspname = 'pg_toast' THEN CASE c.relkind
WHEN 'r' THEN 'SYSTEM TOAST TABLE'
WHEN 'i' THEN 'SYSTEM TOAST INDEX'
ELSE NULL
END
ELSE CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE'
WHEN 'i' THEN 'TEMPORARY INDEX'
ELSE NULL
END
END
WHEN false THEN CASE c.relkind
WHEN 'r' THEN 'TABLE'
WHEN 'i' THEN 'INDEX'
WHEN 'S' THEN 'SEQUENCE'
WHEN 'v' THEN 'VIEW'
ELSE NULL END
ELSE NULL
END
AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME

Hi Martin,

I ran the query in Ataccama DQ Analyzer.

The query properly lists the tables in the public schema. But these tables are still not listed in the database connection tree in the left panel.

See attached image.

Thanks,

Olivier.