Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

auto-increment column in PostgreSQL on the fly?

Tags:

postgresql

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?

like image 284
thor Avatar asked Apr 26 '16 00:04

thor


1 Answers

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.

like image 184
donkopotamus Avatar answered Oct 13 '22 13:10

donkopotamus