distal-attribute
distal-attribute
distal-attribute
distal-attribute

Table duplication in PostgreSQL

cduprez posted 4 months ago in General
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
ansgar posted 4 months ago
Done in r4806
cduprez posted 4 months ago
Thank you very muck.
Great job and reactivity !

Regards,

Cedric

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