Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

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

share|improve this answer
    
Cool - I'll give it ago and get back to you. –  Ando Jul 16 '11 at 0:04
add comment

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;
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
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
add comment

I don't think that is possible. You could create temporary table and add unique column to that table but then you have a bad database design if your data is not static. Also you could create a function that would create combination of some ID's or other columns from all tables that are used in the view and then add this function to your view. It is not perfect but could work for you.

share|improve this answer
add comment

Your Answer

 
discard

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.