View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0002241 | Database Workbench v6 | PostgreSQL Module | public | 2024-06-26 13:57 | 2024-07-15 14:06 |
| Reporter | Svein Erling Tysvær | Assigned To | Martijn Tonies | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 6.5.0 | ||||
| Fixed in Version | 6.5.2 | ||||
| Summary | 0002241: Non-public-schema relations don't include schema name in SQL Insight | ||||
| Description | Typing '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 Information | This 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). | ||||
| Tags | No tags attached. | ||||
|
|
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. |
|
|
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. |
| 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 |
