Bug report #13150

QGIS fails to load postgis VIEW Table

Added by baditaflorin - over 4 years ago. Updated over 4 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:-
Affected QGIS version:2.8.2 Regression?:No
Operating System:windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:invalid
Crashes QGIS or corrupts data:No Copied to github as #:21213

Description

this is applied on a imported osm.pbf file, using osmosis.

the command to create the view is

CREATE OR REPLACE VIEW nodes_view AS
SELECT
id, user_id, tstamp, changeset_id, geom,
tags->'addr:housenumber' As addr_housenumber,
tags->'tourism' As tourist_attraction
FROM nodes
WHERE ST_GeometryType(geom) = 'ST_Point';
2015-07-24T11:51:52    0    QGIS starting...
2015-07-24T11:51:52    0    Python support ENABLED :-) 
2015-07-24T11:51:57    0    Application state:
            QGIS_PREFIX_PATH env var:        C:/PROGRA~1/QGISWI~1/apps/qgis
            Prefix:        C:/PROGRA~1/QGISWI~1/apps/qgis
            Plugin Path:        C:/PROGRA~1/QGISWI~1/apps/qgis/plugins
            Package Data Path:    C:/PROGRA~1/QGISWI~1/apps/qgis/.
            Active Theme Name:    default
            Active Theme Path:    :/images/themes/default/
            Default Theme Path:    :/images/themes/default/
            SVG Search Paths:    C:/PROGRA~1/QGISWI~1/apps/qgis/./svg/
                    C:/Users/florinb/.qgis2//svg/
            User DB Path:    C:/PROGRA~1/QGISWI~1/apps/qgis/./resources/qgis.db

2015-07-24T11:51:57    0    QGIS Ready!
2015-07-24T14:00:10    1    dbname='CA-addr-all-240715-fl-osms42' host=map-analyst port=5432 user='user' password='pass' sslmode=disable key='changeset_id' srid=4326 type=POINT table="public"."nodes_view" (geom) sql= is an invalid layer - not loaded

History

#1 Updated by baditaflorin - over 4 years ago

i think it`s because of this, how do i add a primary key ?

2015-07-24T14:37:53    1    Primary key field 'changeset_id' for view not unique.
2015-07-24T14:37:53    1    PostgreSQL layer has no primary key.

#2 Updated by Jürgen Fischer over 4 years ago

  • Resolution set to invalid
  • Priority changed from Severe/Regression to Normal
  • Status changed from Open to Closed

baditaflorin - wrote:

i think it`s because of this, how do i add a primary key ?

[...]

Doesn't nodes have one? Include it in the view - and selected it as primary key (key='whateveritis' instead of key='changeset_id'). You could also add row_number() OVER () AS whateveritis and select that.

Also available in: Atom PDF