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

Table duplication in PostgreSQL

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

Regards,

Cedric

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