0

QGIS 3 has a new "Data Source manager" and I was hoping that it gave a better mechanism to load layers from PostGIS views but it would appear that it is just a wrapper around the old stuff that was exposed in the tool bar. Worse still it no longer recognises the initial column as feature_id if it has unique values in the column which means one has to fiddle with the feature_id selection which is highly non intuitive.

Am I missing something?

1 Answer 1

1

What I missed was the new "browser" item at the top of the column:

new browser option showing expanded PostGIS tree

This lists all the items without all the confusing clutter in the old dialogue which you get if you select "PostgresSQL". Simply double clicking the layer will load it if it is a valid layer and the first column contains unique feature ids.

To add unique feature ids to an existing query I use:

drop view <view name>;

create OR REPLACE view <view name> as
    SELECT row_number() over () AS _uid_, * FROM SELECT <rest of query>

This will insert a column _uid_ at the start of the table that QGIS can use as the "feature_id". There is nothing magical about the name _uid_ it is just what I use as it won't conflict with any other column names that I might use.

It is not clear to me if the fact that the 'old' dialogue no longer automatically picks up the first column of unique values is a bug or a feature.

1
  • 1
    the over clause should contain an order by statement including columns that can be used to uniquely identify each row (i.e. candidates for a primary key / composite primary key). Otherwise the row order can be different when fetching just the geometry + id (for display) and when loading the attribute table (all fields), making it impossible to select a feature to see its attributes (same ID pointing to different rows)
    – JGH
    Commented Apr 8, 2018 at 1:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.