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?
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With