View Issue Details

IDProjectCategoryView StatusLast Update
0002241Database Workbench v6PostgreSQL Modulepublic2024-07-15 14:06
ReporterSvein Erling Tysvær Assigned ToMartijn Tonies  
Status closedResolutionfixed 
Product Version6.5.0 
Fixed in Version6.5.2 
Summary0002241: Non-public-schema relations don't include schema name in SQL Insight
DescriptionTyping 'SELECT * FROM ' in the SQL Editor and waiting a second let me choose which table to select from. If I select an existing table and try to run the query, I get 'Relation <table name> does not exist.' Naturally, the same happens if I type the table name by hand.

The reason is that PostgreSQL requires the table name to be prefixed by databasename. I.e. 'SELECT * FROM Tonies' fails, whereas 'SELECT * FROM Martijn.Tonies' succeeds (provided that the database is named Martijn and the table Tonies). The error is very easy to avoid, and I'm not certain whether it is an error or if it is due to me not knowing how to use PostgreSQL through Database Workbench.
Additional InformationThis database is PostgreSQL 13.9. Another thing that surprised me was that I could choose between 42 table names, but only 39 are shown in Database Workbench if I expand Tables. I guess the tables I'm able to choose that are not amongst the tables could be tables that used to exist, but that have been dropped (all 3 table names start achilles_ and I've heard achilles being mentioned before).
TagsNo tags attached.


Martijn Tonies

2024-06-26 16:08

administrator   ~0000612

Hello Set,

I believe your reasoning to be incorrect.

PostgreSQL does not require the database name, it -might- require the schema name, but only for the non-default schema. It is possible you have multiple schemas in your database(s). The default schema is PUBLIC.

That means any table in schema PUBLIC should be accessible using
SELECT * FROM mytable

but in another schema, you should use
SELECT * FROM myschema.mytable

The SQL & Parameter Insight settings have a drop down box for when to include a schema, but it appears not to make any difference for PostgreSQL. This, of course, would be a bug.

Can you confirm you're using an non-default schema? If not, then please show screenshots.

Svein Erling Tysvær

2024-06-26 16:55

reporter   ~0000613

I can confirm that I'm not using public (public doesn't have any tables), so yes, it would be a non-default schema. My reasoning may be incorrect, but it is a simple as 'if DBW shows me a list of tables to select from, it should not create an error if selecting one and then try to run it. It doesn't matter to me whether the editor runs SELECT * FROM mytable or if DBW automatically assigns myschema.mytable when I select from its suggestions, but it puzzles me that it creates an error.

Issue History

Date Modified Username Field Change
2024-06-26 13:57 Svein Erling Tysvær New Issue
2024-06-26 16:08 Martijn Tonies Assigned To => Martijn Tonies
2024-06-26 16:08 Martijn Tonies Status new => feedback
2024-06-26 16:08 Martijn Tonies Note Added: 0000612
2024-06-26 16:55 Svein Erling Tysvær Note Added: 0000613
2024-06-26 16:55 Svein Erling Tysvær Status feedback => assigned
2024-06-27 09:49 Martijn Tonies Priority low => normal
2024-06-27 09:49 Martijn Tonies Summary Relation does not exist => Non-public-schema relations don't include schema name in SQL Insight
2024-06-27 10:46 Martijn Tonies Status assigned => resolved
2024-06-27 10:46 Martijn Tonies Resolution open => fixed
2024-06-27 10:46 Martijn Tonies Fixed in Version => 6.5.2
2024-07-15 14:06 Martijn Tonies Status resolved => closed