In PostGIS is it possible to create a View with a unique ID?

Question

When I create a view in PostGIS is there any way to add a unique ID to that view? Just like the "gid" field in any other PostGIS table?

Edit: Sorry I should have included this in the original post. I am using PostGresql 9.0 and PostGIS 1.5.

Ando

geographika · Accepted Answer · 2011-07-15 09:51:33Z

35

You should be able to use the row_number() function as a column in your view. This works for Postgres 8.4 or higher.

SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY column_to_sort_by ASC) AS ROW_NUMBER,
    Col1, Col2
  FROM table_name
) myview_name

This should work in most databases including SQL Server, Oracle, and MySQL.

answered Jul 15, 2011 at 9:36

geographika

14.4k4 gold badges55 silver badges79 bronze badges

Cool - I'll give it ago and get back to you.
– Ando
Commented Jul 16, 2011 at 0:04
8

You also can do row_number() OVER() AS "id" without ORDER over column
– falcacibar
Commented Feb 12, 2016 at 17:43
This is usually a very poor design decision, as row_number is effectively a random value. You can't depend on it being consistent between SELECTs, particularly when the underlying data changes.
– jpmc26
Commented Jan 25, 2019 at 1:38
@jpmc - by its very nature a view will change if its underlying data changes. A reliable ID isn't part of the question requirements. Some GISs simply require a unique ID for each feature just to load the layer (e.g. ArcMap).
– geographika
Commented Jan 25, 2019 at 13:18
I would not trust ArcMap to function properly with an inconsistent ID. How do you know it doesn't requery the database when you pan but not refresh the attribute table? Then you'd have a mismatch trying to select features.
– jpmc26
Commented Jan 25, 2019 at 15:02

Add a comment |

Mike T · Accepted Answer · 2011-07-16 23:44:59Z

6

For older versions of PostgreSQL (<= 8.3), here is another solution. In this example, I use another column name vid for "view ID".

First, create a sequence. Use CYCLE so that it will loop in the event you reach the end of the sequence.

CREATE SEQUENCE myview_vid_seq CYCLE;

Now, create a VIEW that uses the sequence:

CREATE OR REPLACE VIEW myview AS 
 SELECT nextval('myview_vid_seq'::regclass) AS vid, mytable.*
 FROM mytable;

answered Jul 16, 2011 at 23:44

Mike T

42.4k10 gold badges129 silver badges190 bronze badges

I haven't been back into work to try these suggestions yet. But the solution you have suggested is this only for Postgresql versions <= 8.3? I am running Postresql 9.0 and PostGIS 1.5.
– Ando
Commented Jul 17, 2011 at 2:53
2

For your system, both this and geographika's answer should work. It would be a good performance test to see which one is faster. I've had to do what you ask in your question to view data in QGIS, so I'm guessing you are in a similar situation.
– Mike T
Commented Jul 17, 2011 at 4:59
1

Identical situation I would say - Trying to combine a few layers in a view and require an unique id for QGIS.
– Ando
Commented Jul 17, 2011 at 7:37

Add a comment |

2 Answers 2