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.



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


    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.

  • Cool - I'll give it ago and get back to you.
    – Ando
    Jul 16 '11 at 0:04
  • 7
    You also can do row_number() OVER() AS "id" without ORDER over column
    – falcacibar
    Feb 12 '16 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
    Jan 25 '19 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). Jan 25 '19 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
    Jan 25 '19 at 15:02

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.


Now, create a VIEW that uses the sequence:

 SELECT nextval('myview_vid_seq'::regclass) AS vid, mytable.*
 FROM mytable;
  • 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
    Jul 17 '11 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
    Jul 17 '11 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
    Jul 17 '11 at 7:37

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.