Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
up vote 21 down vote accepted

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.

share|improve this answer
Cool - I'll give it ago and get back to you. – Ando Jul 16 '11 at 0:04
You also can do row_number() OVER() AS "id" without ORDER over column – falcacibar Feb 12 at 17:43

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;
share|improve this answer
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
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
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 posting your answer, you agree to the privacy policy and terms of service.

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