Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

PostgreSQL does not show databases

USvER's profile image USvER posted 5 years ago in General Permalink
When i connect to my Postgre server it uses
SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname";

But what should be is
SELECT datname FROM pg_database


Is this related to this http://www.heidisql.com/forum.php?t=16213
michaeltomiak's profile image michaeltomiak posted 5 years ago Permalink
I am having the same issue.
kanidrive's profile image kanidrive posted 4 years ago Permalink

I'm also having a very similar issue, only I see all the databases, but there are no tables shown for the "public" database in the left panel. It just shows as 0B in size,

The Select statement that has no results in my case is: SELECT "p"."proname" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'pg_catalog'; SET search_path TO E'public';

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
kanidrive's profile image kanidrive posted 4 years ago Permalink

see also:

/ Entering session "Postgres Dev" / SELECT , pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"=E'public'; / Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. / SELECT "p"."proname" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'public'; SET search_path TO E'pg_toast_temp_1', E'public'; SELECT , pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"=E'pg_toast_temp_1'; / Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. / SELECT "p"."proname" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'pg_toast_temp_1'; SELECT , pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"=E'information_schema'; / Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. / SELECT "p"."proname" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'information_schema'; SELECT , pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || E'.' || QUOTE_IDENT(t.TABLE_NAME))::bigint 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"=E'pg_catalog'; / Unknown datatype oid #1034 for "nspacl". Fall back to UNKNOWN. / / Unknown datatype oid #1034 for "relacl". Fall back to UNKNOWN. / / Unknown datatype oid #1009 for "reloptions". Fall back to UNKNOWN. / SELECT "p"."proname" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"=E'pg_catalog'; SET search_path TO E'public';

USvER's profile image USvER posted 4 years ago Permalink

Still having the same problem with postgres...

ansgar's profile image ansgar posted 4 years ago Permalink

USvER, are on the latest HeidiSQL build?

USvER's profile image USvER posted 4 years ago Permalink

I'm not sure it's latest build: r5104

ansgar's profile image ansgar posted 4 years ago Permalink

Ok, r5104 is the latest one.

So, what does the query return if not your databases?

SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname";
USvER's profile image USvER posted 4 years ago Permalink

So, what does the query return if not your databases?

I'm not database expert, i'm sure that there is some usefull information returned by that request, and with that i can browse functions in the postgres etc etc. But there is no database, even created by HeidiSQL itself. I will add screenshots from HeidiSQL and pgAdmin to compare.

2 attachment(s):
  • Screenshot_40
  • Screenshot_41
USvER's profile image USvER posted 4 years ago Permalink

So, pgAdmin shows me two databases TEST and postgres HeidiSQL shows me some internal databases/tables with posgres internal stuff like aggregate functions, views etc etc

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
wouter_van_nifterick's profile image wouter_van_nifterick posted 4 years ago Permalink

Just some input from somebody who uses both PgAdminIII and HeidiSQL regularly.

HeidiSQL only shows the schema's within the database that was specified in the connection manager. That's probably HeidiSQL was originally made with MySQL in mind, where this extra level does not exist.

  • database1
  • schemaA
    • table A
    • table B
    • table C
  • schemaB
    • table D
    • table E
  • database2
    • schemaC
      • table F

It actually all works fine here. The connection window correctly shows a list of existing databases as a dropdown, if you provide correct connection details.

It would be nice if the database level would be an extra level in the dbtree, but if you don't have too many databases, this works out fine.

wouter_van_nifterick's profile image wouter_van_nifterick posted 4 years ago Permalink

So, pgAdmin shows me two databases TEST and postgres HeidiSQL shows me some internal databases/tables with posgres internal stuff like aggregate functions, views etc etc

What you see are not two databases, but two schema's.

You didn't specify which database you want to connect to in the connection manager. Click on the triangle in the database field there to see which ones are available.

Because you didn't specify any, you were probably connected to the "postgres" database, which looks exactly like what you describe.

So, to put it short: specify a database name via the connection manager. That should resolve all of your issues.

USvER's profile image USvER posted 4 years ago Permalink

Yeah! That worked! Thanks!

w00kie's profile image w00kie posted 4 months ago Permalink

I actually put my database name in into the connection window, and I still only see what looks like the "postgres" database.

tzar's profile image tzar posted 1 month ago Permalink

This is still broken!

I remove the database with DROP DATABASE PUBLIC - it's gone from left panel Now I create it again with CREATE DATABASE PUBLIC - it doesn't show up on left panel! Please fix it!

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.