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

Mark Cave-Ayland mark.cave-ayland at ilande.co.uk
Fri Mar 30 03:08:21 PDT 2007


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.





More information about the postgis-users mailing list