8

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?

Community
  • 1
  • 1
thor
  • 21,418
  • 31
  • 87
  • 173
  • 2
    Postgresql has CREATE SEQUENCE. You can get incrementing numbers from those. – Thilo Apr 26 '16 at 00:46
  • @tinlyx Can you give more details on the uniqueness you require ... be it locally within a query versus globally across queries – donkopotamus Apr 26 '16 at 04:22
  • Please post your code. You've been around SO long enough to know how to ask a proper question. There are various solutions depending on how your statement is constructed. Without more information you will get nothing but wild hunches. – Patrick Apr 26 '16 at 04:28
  • Do you require an integer? If not md5(row_to_json(x)::text) would work as a key (or a pg_crypto hash of some kind) – Joe Love Apr 26 '16 at 16:50
  • @JoeLove and Patrick, please see my update regarding the use case/requirement. – thor Apr 26 '16 at 18:19
  • @tinlyx Do you need the `id` to be consistent across different executions of this query? That is, should a particular `geom` always be assigned the same `id`, or not? – donkopotamus Apr 26 '16 at 23:03

1 Answers1

14

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.

donkopotamus
  • 22,114
  • 2
  • 48
  • 60