Bug report #18244
DB Manager: "Update SQL layer" error
| Status: | Closed | ||
|---|---|---|---|
| Priority: | High | ||
| Assignee: | - | ||
| Category: | DB Manager | ||
| Affected QGIS version: | 3.0.0 | Regression?: | Yes |
| Operating System: | Easy fix?: | No | |
| Pull Request or Patch supplied: | No | Resolution: | fixed/implemented |
| Crashes QGIS or corrupts data: | No | Copied to github as #: | 26135 |
Description
new description:
open db manager
connect to a db
open SQL window
make a query like SELECT * FROM schema.table
add result to project as SQL layer
right click on SQL layer and choose "update SQL layer"
and error will show like:
relation "SELECT * FROM schema.table
" does not exist
works ok on 2.18
old description:
I add a layer by connecting to postgis. When using "Update SQL layer..." a dialog opens with the expected query: SELECT * FROM "kopavogur"."byggingar".
Now I modify the query in the dialog so that it reads: SELECT * FROM "kopavogur"."byggingar" where id<100
I click execute and it works fine. I click Update and the filtered data is correctly shown on the canvas.
Now when I want to modify the query again by selecting "Update SQL layer..." DB Manager opens with the following error message:
relation "SELECT * FROM "kopavogur"."byggingar" where id<100
" does not exist
LINE 1: SELECT * FROM "SELECT * FROM ""kopavogur"".""byggingar"" whe...
^
When I close the error dialog, the query has been modified to read:
SELECT * FROM "SELECT * FROM ""kopavogur"".""byggingar"" where id<100
"
Error messages persist if I try to modify the query.
Related issues
History
#1
Updated by Arni Geirsson over 6 years ago
- Assignee deleted (
Arni Geirsson)
#2
Updated by Giovanni Manghi over 6 years ago
- Subject changed from Update SQL layer to DB Manager: "Update SQL layer" error
- Description updated (diff)
- Regression? changed from No to Yes
- Priority changed from Normal to High
- Operating System deleted (
Win 10)
updated subject and description
#3
Updated by Daniel Possenriede over 6 years ago
I can confirm this error. QGIS 3.0.1 on Windows 10 connected to PostgreSQL 9.6 with Postgis 2.3.
The problem is that QGIS adds SELECT * FROM " ... " to the original query (... is the original query). It also duplicates all double quotes in the query.
#4
Updated by Ger Groeneveld over 6 years ago
Update goes wrong when the layer is in edit mode. Afterwards the edit is switched off and can't be enabled anymore.
Quick solution: Switch of edit mode and answer yes to "save changes", postgresql table does get updated (see db manager, table content).
(QGIS 3.0.3; Postgresql 10, Linux 4.15/Qt5/Ubuntu 18.04)
DON'T FORGET or else one has to restart QGIS again, log in, and copy all the settings for that layer again. Obvious the update SQL layer should switch of edit mode and re-enable again
#5
Updated by Samuel Wechsler about 6 years ago
I can confirm the error observed by Arni Geirsson. This only appeared with QGIS3 it was properly working in QGIS2.18. I'd be very glad if this could be fixed. Our institution switched from QGIS 2.18 to QGIS 3.2 and it is a bit a pain to explain to end users why things that used to work don't anymore.
#6
Updated by Giovanni Manghi about 6 years ago
Samuel Wechsler wrote:
I can confirm the error observed by Arni Geirsson. This only appeared with QGIS3 it was properly working in QGIS2.18. I'd be very glad if this could be fixed. Our institution switched from QGIS 2.18 to QGIS 3.2 and it is a bit a pain to explain to end users why things that used to work don't anymore.
if is a blocker for your organization please consider support the work needed to make the fix, or eventually submit a patch.
#7
Updated by Jürgen Fischer about 6 years ago
- Duplicated by Bug report #19843: Update SQL Layer converts query to table name added
#8
Updated by Paolo Cavallini almost 6 years ago
Could you please check again on current release?
Thanks.
#9
Updated by Giovanni Manghi almost 6 years ago
- Status changed from Open to Feedback
Paolo Cavallini wrote:
Could you please check again on current release?
Thanks.
Please change status to "feedback" when needed.
#10
Updated by Arni Geirsson almost 6 years ago
Giovanni Manghi wrote:
Paolo Cavallini wrote:
Could you please check again on current release?
Thanks.Please change status to "feedback" when needed.
Hi
This seems to be resolved. I changed the query multiple times and it works as expected.
Thanks!
Arni
#11
Updated by Arni Geirsson almost 6 years ago
Arni Geirsson wrote:
Giovanni Manghi wrote:
Paolo Cavallini wrote:
Could you please check again on current release?
Thanks.Please change status to "feedback" when needed.
Hi
This seems to be resolved. I changed the query multiple times and it works as expected.
Thanks!Arni
I forgot to add that I am using 3.4.3
Arni
#12
Updated by Arni Geirsson almost 6 years ago
I notice the following and wonder if it is the intended way.
I create a query in DB Manager: select id, st_buffer(geom,10) as geom from kopavogur.byggingar where id<100
I load the result as a new layer using the id and geom and it works well.
Now I right-click the layer and select "Update SQL Layer..." and the query dialog opens again, but with the query modified, as follows:
(SELECT row_number() over () AS uid,* FROM (select id, st_buffer(geom,10) as geom from kopavogur.byggingar where id<100 ) AS subq_1 )
I can modify this but must remember to check again the field labelled "Column(s) with unique values" as it now has the value "_uid_" but grayed out. The modification will not load until I have rechecked the check box.
Arni
#13
Updated by Arni Geirsson almost 6 years ago
Another observation that had me confused for a while:
When modifying the query by putting st_buffer around the line geometry, obviously a polygon layer would result but the original layer continued to be listed in the layer list as a line layer and nothing was shown on the canvas. I suppose QGIS was unable to handle the fact that a layer that was originally a line layer had been transformed into a polygon layer by updating the query. Perhaps a warning of some sort could be issued.
Arni
#14
Updated by Giovanni Manghi almost 6 years ago
- Resolution set to fixed/implemented
- Status changed from Feedback to Closed
#15
Updated by Samuel Wechsler almost 6 years ago
I can also confirm the fix. Works as expected. Thanks guys!