Table duplication in PostgreSQL

[expired user #8285]'s profile image [expired user #8285] posted 10 years ago in General Permalink
Hi,

First of all, congratulation for all the work to try to incorporate PostgreSQL in HeidiSQL. This is great !

I am testing the last (at that time) r4805 version.
My PostgreSQL version is 9.3.5, on Windows 7 64 bits.

On my database, I have several schemas, some of them with tables that have the same name (but inside different schemas, this is not a problem).
Example :
schema1
- mytable1
- mytable2
schema2
- mytable1
- mytable3

In the database browser, in my schema1 (and also in schema2), I see "mytable1" duplicated.

The problem seems to come from this query, executed to get the tables list of a schema :
SELECT *
, pg_table_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS data_length, pg_relation_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS index_length
, c.reltuples, obj_description(c.oid) AS comment 
FROM "information_schema"."tables" AS t 
LEFT JOIN "pg_class" c ON c.relname=t.table_name 
LEFT JOIN "pg_namespace" n ON (n.oid = c.relnamespace) 
WHERE t."table_schema"='schema1';

A reference on the schema name is missing between "information_schema"."tables" and "pg_class".

The following query gives a more suitable result :
SELECT *
, pg_table_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS data_length, pg_relation_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS index_length
, c.reltuples, obj_description(c.oid) AS comment 
FROM "information_schema"."tables" AS t 
LEFT JOIN "pg_namespace" n ON t.table_schema = n.nspname
LEFT JOIN "pg_class" c ON n.oid = c.relnamespace AND c.relname=t.table_name 
WHERE t."table_schema"='schema1';

There is no more duplication of tables.

Could you try to implement it ?

Thanks again for all,

Cedric
Code modification/commit from ansgar.becker, 10 years ago, revision 4806
Fix duplicated tables in database view on PostgreSQL. See http://www.heidisql.com/forum.php?t=16179#p16179
ansgar's profile image ansgar posted 10 years ago Permalink
Done in r4806
[expired user #8285]'s profile image [expired user #8285] posted 10 years ago Permalink
Thank you very muck.
Great job and reactivity !

Regards,

Cedric

Please login to leave a reply, or register at first.