Switch to "Identity" columns instead of "Serial" for PostgreSQL 10+

[mboeringa]

PostgreSQL 10 introduced the "Identity" column constraint for unique ID columns with SQL standard definition ("GENERATED ALWAYS / BY DEFAULT AS IDENTITY"), which has several advantages over the "serial" type. Maybe it would be an idea to at least allow specifying this sql_type instead of serial, or default to it for PostgreSQL 10+:

https://www.depesz.com/2017/04/10/waiting-for-postgresql-10-identity-columns/

https://www.postgresql.org/docs/current/sql-createtable.html

[joto]

You can use any sql_type you like, this is the point of the sql_type setting. I am putting something in the manual that this is similar to a serial column.

[joto]

Yes, i believe it should work, but we will not guarantee that it will always work.

[mboeringa]

Ok, I will try it out and see if I can get it working, and report back the result here.

[mboeringa]

@joto

You are right, this is already supported.

My first attempt failed though:

But I then realized that the code should read:

column = 'ID', sql_type = 'bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY', create_only = true

to prevent the not-null constraint error, as properly documented on the manual pages.

After changing that, both 'serial' and the 'identity' clauses worked.

I did realize there is another reason good reason to switch to 'identity' though: I noticed in DBeaver it appears the 'serial' column type is created as 'int4' by default by PostgreSQL. While not an immediate concern, it is clear though that eventually one may run out of values with 'int4' and the ever growing OpenStreetMap database... It is not without reason osm_id has been 'int8' for quite some time.

[rustprooflabs]

I did realize there is another reason good reason to switch to 'identity' though: I noticed in DBeaver it appears the 'serial' column type is created as 'int4' by default by PostgreSQL. While not an immediate concern, it is clear though that eventually one may run out of values with 'int4' and the ever growing OpenStreetMap database... It is not without reason osm_id has been 'int8' for quite some time.

@mboeringa Using bigserial instead of serial will create int8. IDENTITY is still the preferred method though, it conforms to the SQL standard and makes permissions easier to manage.

[mboeringa]

@rustprooflabs, thanks. As I showed and Jochen stated, "identity" is already usable with current v1.5.0 of osm2pgsql, so that is good to know for style developers.