2


Is it possible to create a new id serial column inside a view? I need it to be able to add my view in postgis

I have made a full outer join on two tables
I need a unique PK to load the view in QGIS.
Creating a id serial does not help in this case because it will become redundant once the two tables are joined.

This is my view:

CREATE OR REPLACE VIEW myview AS
SELECT 
 table1.id
 table1.name
 table2.id
 table2.street
FROM table1
 FULL OUTER JOIN table2
 ON table1.id  = table2.id

I have seen some ROW_NUMBER examples but i dont know how to script it when I am using a join.In PostGIS is it possible to create a View with a unique ID?


a final try before I find another direction to solve my problem. It is a try to use the row_number to create a unique column inside my VIEW. It looks daunting and it does not work.. probably not the best way of solving my problem (Iam not very experienced writing script yet.. but i give it a last try in case it is just a quick fix)

create view testView as (
    select
    row_number() OVER(ORDER BY (SELECT 
 table1.id
 table1.name
 table2.id
 table2.street
FROM table1
 FULL OUTER JOIN table2
 ON table1.id  = table2.id) AS OID,
   table1.id
 table1.name
 table2.id
 table2.street
FROM table1
 FULL OUTER JOIN table2
 ON table1.id  = table2.id
  • Would it be possible to use uuid from postgresql.org/docs/9.4/static/uuid-ossp.html and add it to the SELECT statement? – Michal Zimmermann Mar 12 '15 at 14:23
  • @geogrow can you verify your sql is correct? It looks like you're selecting from a table named columnA and you're pulling columns named table1, table2, table3... – DPSSpatial Mar 12 '15 at 14:42
  • Sorry, I realize that I wrote the sql incorrect. I hope it make more sense now. It is basically just a standard case of a full outer join – geogrow Mar 12 '15 at 14:48
6

Row_Number works well if you have data returned that will allow you to order your results in a unique way, that is, duplicates in the library_na field below will give duplicate OID values from the row_number function:

create view testView as (
    select
    row_number() OVER(ORDER BY library_na DESC) AS OID
    , library_na
    , geom
    from public.libraries
)

which returns:

enter image description here

(sorry that screen cap looks bad...)

  • Thanks! I have used that in another situation, but I am confused to how I should write the script when i have already done a full outer join. I dont understand how I add the select row_number within the select of the columns. – geogrow Mar 12 '15 at 14:39
  • @geogrow it should just be the first line in your SQL, or, you can wrap a sub-query around your join query and use the row_number there... see my comment above. – DPSSpatial Mar 12 '15 at 14:42
  • @geogrow did you ever get this working? – DPSSpatial Jan 11 '16 at 21:11
  • Yes thanks eventually I did. I used a subquery to UNION 3 tables with similar structure and then used row_number to go on top of it to get a unique ID for each row. – geogrow Jan 12 '16 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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