PostgreSQL does not show databases

[expired user #8953]'s profile image [expired user #8953] posted 9 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
[expired user #8877]'s profile image [expired user #8877] posted 9 years ago Permalink
I am having the same issue.
kanidrive's profile image kanidrive posted 8 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';

kanidrive's profile image kanidrive posted 8 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';

[expired user #8953]'s profile image [expired user #8953] posted 8 years ago Permalink

Still having the same problem with postgres...

ansgar's profile image ansgar posted 8 years ago Permalink

USvER, are on the latest HeidiSQL build?

[expired user #8953]'s profile image [expired user #8953] posted 8 years ago Permalink

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

ansgar's profile image ansgar posted 8 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";
[expired user #8953]'s profile image [expired user #8953] posted 8 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
[expired user #8953]'s profile image [expired user #8953] posted 8 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

[expired user #5213]'s profile image [expired user #5213] posted 8 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.

[expired user #5213]'s profile image [expired user #5213] posted 8 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.

[expired user #8953]'s profile image [expired user #8953] posted 8 years ago Permalink

Yeah! That worked! Thanks!

w00kie's profile image w00kie posted 4 years 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 4 years 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!

alex1312's profile image alex1312 posted 1 year ago Permalink

It's been 7 years, are there any plans to implement this? Looking at the console I don't see it querying pg_database at all.

/* Delimiter changed to ; */ /* Connecting to [WHATEVER] via PostgreSQL (TCP/IP), username [WHATEVER], using password: Yes ... */ SELECT NOW(); SELECT VERSION(); SET statement_timeout TO 30000; SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER; SHOW ssl; /* Connected. Thread-ID: 91126 */ /* Reading function definitions from C:\Program Files\HeidiSQL\functions-postgresql.ini */ SELECT table_name FROM information_schema.tables WHERE table_schema='information_schema'; SELECT "nspname" FROM "pg_catalog"."pg_namespace" ORDER BY "nspname"; SET search_path TO 'pg_catalog', '$user', 'public'; /* Entering session "[WHATEVER]" */ SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || 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"='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. */ /* Unknown datatype oid #194 for "relpartbound". Fall back to UNKNOWN. */ SELECT "p"."proname", "p"."proargtypes" FROM "pg_catalog"."pg_namespace" AS "n" JOIN "pg_catalog"."pg_proc" AS "p" ON "p"."pronamespace" = "n"."oid" WHERE "n"."nspname"='pg_catalog'; /* Loading file "C:\Users\[WHATEVER]\AppData\Roaming\HeidiSQL\Backups\query-tab-2023-01-27_19-12-38-941.sql" (117 B) into query tab #1 ... */ /* Loading file "C:\Users\[WHATEVER]\AppData\Roaming\HeidiSQL\Backups\query-tab-2023-01-28_01-56-40-973.sql" (26 B) into query tab #2 ... */ /* Scaling controls to screen DPI: 100% */

My user does have permission to list databases as you can see in the attached screenshot. I'm using HeidiSQL 12.3.0.6655 which appears to be the latest build at the time of me posting this.

1 attachment(s):
  • heidsql
ansgar's profile image ansgar posted 1 year ago Permalink

Same report as in issue #1560.

HeidiSQL displays schemata in PostgreSQL mode, not databases. You normally should set the database name in the session manager:

Description

alex1312's profile image alex1312 posted 1 year ago Permalink

Ok I guess that actually makes sense since PostgreSQL requires a separate connection for each database

designlee's profile image designlee posted 6 months ago Permalink

Same report as in issue #1560.

HeidiSQL displays schemata in PostgreSQL mode, not databases. You normally should set the database name in the session manager:

Description

It's still doesn't work.lastest version 12.6.0.6765.

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