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

up vote 28 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.

http://www.postgresql.org/docs/current/static/functions-window.html

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.

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

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;
  • 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
  • 1
    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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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