Skip to main content
Geographic Information Systems

Return to Question

Remove unrelated content as the answer is techniically answered now. I'll open a new thread for the new question.
Source Link
swiss_knight
swiss_knight
  • 10.9k
  • 9
  • 49
  • 132

EDITs

After some word exchanges with JGH (see comments below) I've noticed that testing the query without the WHERE statement is giving encouraging results:

enter image description here

But then, when trying to actually "Add" the layer, QGIS freezes and is no more responsive. I have to kill it (tried to wait 20min or so, where the query returns less than 10 small polygons within psql or pgAdmin4).

On the other hand, when the WHERE statement is present, pressing the "Test" button itself makes QGIS unresponsive, the "No error" popup window never comes on the screen and I have to kill the program.

The behaviour is the same in every cases, having the "Embedded Layers" selected or not (in every cases, the required layers are already in the table of content of my project).

Follow up there: Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

EDITs

After some word exchanges with JGH (see comments below) I've noticed that testing the query without the WHERE statement is giving encouraging results:

enter image description here

But then, when trying to actually "Add" the layer, QGIS freezes and is no more responsive. I have to kill it (tried to wait 20min or so, where the query returns less than 10 small polygons within psql or pgAdmin4).

On the other hand, when the WHERE statement is present, pressing the "Test" button itself makes QGIS unresponsive, the "No error" popup window never comes on the screen and I have to kill the program.

The behaviour is the same in every cases, having the "Embedded Layers" selected or not (in every cases, the required layers are already in the table of content of my project).

Follow up there: Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

added 728 characters in body
Source Link
swiss_knight
swiss_knight
  • 10.9k
  • 9
  • 49
  • 132

EDITs

After some word exchanges with JGH (see comments below) I've noticed that testing the query without the WHERE statement is giving encouraging results:

enter image description here

But then, when trying to actually "Add" the layer, QGIS freezes and is no more responsive. I have to kill it (tried to wait 20min or so, where the query returns less than 10 small polygons within psql or pgAdmin4).

On the other hand, when the WHERE statement is present, pressing the "Test" button itself makes QGIS unresponsive, the "No error" popup window never comes on the screen and I have to kill the program.

The behaviour is the same in every cases, having the "Embedded Layers" selected or not (in every cases, the required layers are already in the table of content of my project).


Here is the version information:

EDITs

After some word exchanges with JGH (see comments below) I've noticed that testing the query without the WHERE statement is giving encouraging results:

enter image description here

But then, when trying to actually "Add" the layer, QGIS freezes and is no more responsive. I have to kill it (tried to wait 20min or so, where the query returns less than 10 small polygons within psql or pgAdmin4).

On the other hand, when the WHERE statement is present, pressing the "Test" button itself makes QGIS unresponsive, the "No error" popup window never comes on the screen and I have to kill the program.

The behaviour is the same in every cases, having the "Embedded Layers" selected or not (in every cases, the required layers are already in the table of content of my project).


Here is the version information:

Source Link
swiss_knight
swiss_knight
  • 10.9k
  • 9
  • 49
  • 132

QGIS virtual layer: Query preparation error on PRAGMA table_info(_tview): no such column: t.poly

I am trying to load a virtual layer from two existing PostGIS tables, using a simple LEFT JOIN statement:

SELECT t.id, t.poly
FROM table_t t
LEFT JOIN table_f f ON f.fk_poly_id = t.id 
WHERE fk_i_id = 10;

The table_f is a join table containing an id, and two foreign keys: fk_i_id and fk_poly_id.
The table_t is a standard table containing an id and the poly geometry that I'd like to see in my virtual layer for a feature i (referenced by its foreign key fk_i_id in the join table).

This query is perfectly working in psql or pgAdmin4 and returns me some geometric features (ST_Polygon - POLYGON Z) with their ids.

But when I add the two PostGIS tables that I need into the "Embedded layers" of the "Datasource Manager | Virtual Layer" window and when I paste the query in the dedicated space under, I keep getting this error when clicking either "Add" or the "Test" button:

Query preparation error on PRAGMA table_info(_tview): no such column: t.poly

I don't know what I may be doing wrong?
Is this a know bug?
I already tried to rewrite the query in plenty of way, including giving aliases or not, joining the other way, etc etc. All tests were working on pgAdmin but not in QGIS.

Name Version
QGIS version 3.24.2-Tisler
QGIS code revision 13c1a02865
Qt version 5.15.3
Python version 3.10.4
GDAL/OGR version 3.4.1
PROJ version 8.2.1
EPSG Registry database version v10.041 (2021-12-03)
GEOS version 3.10.2-CAPI-1.16.0
SQLite version 3.37.2
PDAL version 2.3.0
PostgreSQL client version unknown
SpatiaLite version 5.0.1
QWT version 6.1.4
QScintilla2 version 2.11.6
OS version Ubuntu 22.04 LTS