[postgis-users] Creating an ID field in a view

[Mark Cave-Ayland]

On Thu, 2007-03-29 at 20:37 +0200, Jose Gomez-Dans wrote:
> Hi Mark,
> 
> 
> On 3/29/07, Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk> wrote:
> > > for my own purposes), I need to create an unique field per feature. Is
> > > there some function which will add this field and populate it
> > > accordingly? I am not interested in its value, only in the fact that
> > > it has to be unique for each feature in the view.
> > If you don't care if the value changes each time you run the query,
> > simply create a sequence and reference it in your SELECT, e.g.
> 
> Mmmm, this would be OK for just throwing the data into QGIS to view
> it. However, I would also like to do some work with it, so the value
> would need to be "static" (i.e., it should not change when I run the
> query). However, the tip is useful :)
> 
> Cheers,
> Jose


Hi Jose,

In that case you'll need to create a new table as the result of your
query, something like:

CREATE SEQUENCE foo;
CREATE TABLE my_table AS SELECT nextval('foo') AS id, * FROM ....
DROP SEQUENCE foo;

AFAIK it's just not possible to generate a unique id on the fly in this
way because SQL doesn't guarantee ordering without an ORDER BY clause,
and I'm not sure that PostgreSQL sorts are stable.


Kind regards,

Mark.