Add a serial id column inside a view

Question

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 http://www.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_BoycottingGISSE, 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

score 6 · Accepted Answer · answered Mar 12 '15 at 14:25

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...)

answered Mar 12 '15 at 14:25

DPSSpatial_BoycottingGISSE

18,790
4
66
110

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_BoycottingGISSE Mar 12 '15 at 14:42
@geogrow did you ever get this working? – DPSSpatial_BoycottingGISSE 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

Add a serial id column inside a view

1 Answers1

Linked