1

Working with PostgreSQL basic view in QGIS I noticed that:

Select *
from t1
where t1.column = 'some_condition'

Views doesn't support constraints according to this topic: https://stackoverflow.com/questions/11667508/how-to-add-primary-key-to-view

My question is: Is some way to use 1:N relation QGIS with PostreSQL views? When I try to create this relation in QGIS - Project - properties using View + table_relation: not load correctlly.

Otherwise, when I used spatial_table (with PK constraint) + table relation: works correctlly.

Someone have any idea?

2
  • 1
    Postgres DOES support constraints. It won't support adding primary keys to a view, which is a different thing
    – JGH
    Commented Jan 3, 2020 at 23:20
  • That's true. Revised and corrected. Commented Jan 5, 2020 at 11:20

1 Answer 1

2

While you cannot create a primary key on a view, you can create a column having unique distinct values which you can use as a primary key in QGIS.

CREATE OR REPLACE VIEW public.myview AS 
  SELECT ROW_NUMBER() OVER(ORDER BY t1.id, t1.name, t1.blablabla) AS V_UID, 
         t1.*
  FROM t1
  WHERE t1.column = 'some_condition';

What is extremely important is that the order by clause field combination leads to unique combination (that is, no two rows have the same values in those fields)

That being said, this example is a bad one as you could simply put a primary key on t1.

1
  • Thanks for your help @JGH. It worked exactly as I wanted. Greetings Commented Jan 9, 2020 at 16:47

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.