CODEWITHSUNDEEP
postgresql
thor
thor

Reputation: 22460

auto-increment column in PostgreSQL on the fly?

I was wondering if it is possible to add an auto-increment integer field on the fly, i.e. without defining it in a CREATE TABLE statement?

For example, I have a statement:

SELECT 1 AS id, t.type FROM t;

and I am can I change this to 

SELECT some_nextval_magic AS id, t.type FROM t;

I need to create the auto-increment field on the fly in the some_nextval_magic part because the result relation is a temporary one during the construction of a bigger SQL statement. And the value of id field is not really important as long as it is unique.

I search around here, and the answers to related questions (e.g. PostgreSQL Autoincrement) mostly involving specifying SERIAL or using nextval in CREATE TABLE. But I don't necessarily want to use CREATE TABLE or VIEW (unless I have to). There are also some discussions of generate_series(), but I am not sure whether it applies here.

-- Update --

My motivation is illustrated in this GIS.SE answer regarding the PostGIS extension. The original query was:

CREATE VIEW buffer40units AS
SELECT 
   g.path[1] as gid, 
   g.geom::geometry(Polygon, 31492) as geom 
FROM
   (SELECT 
     (ST_Dump(ST_UNION(ST_Buffer(geom, 40)))).* 
   FROM point
) as g;

where g.path[1] as gid is an id field "required for visualization in QGIS". I believe the only requirement is that it is integer and unique across the table. I encountered some errors when running the above query when the g.path[] array is empty.

While trying to fix the array in the above query, this thought came to me:

Since the gid value does not matter anyways, is there an auto-increment function that can be used here instead?

Upvotes: 8

Views: 18252

Answers (1)

donkopotamus
donkopotamus

Reputation: 23176

If you wish to have an id field that assigns a unique integer to each row in the output, then use the row_number() window function:

select 
    row_number() over () as id, 
    t.type from t;

The generated id will only be unique within each execution of the query. Multiple executions will not generate new unique values for id.

Upvotes: 17

Related Questions