David Favro wrote: > A couple of questions about auto-assigned identifier columns, > forgive my ignorance, I'm used to other methods to create IDs... > > 1. If creating a new application [i.e. no "legacy" reasons to do > anything] using PostgreSQL 10, when creating an "auto-assigned > integer ID" column, what are the advantages/disadvantages of using > the 'SERIAL' datatype [or equivalent explicitly created SEQUENCE w/ > nextval() used as default for column] versus the SQL-standard > 'integer GENERATED AS IDENTITY'? All other things being equal, it > would seem a no-brainer to follow the standard. Absolutely. Use GENERATED ALWAYS AS IDENTITY. > 2. When using the SQL-standard 'integer GENERATED AS IDENTITY' > column, after inserting a column, what is the recommended method to > find the ID of the just-inserted row? Is there no SQL-standard way? > The docs seem to imply (without explicitly stating) that a SEQUENCE > is used behind the scenes hence 'currval()' could be used, but I > didn't see in the docs any mention of what the underlying sequence's > name is, or how to specify a name. Perhaps 'lastval()' would work, > but not in all cases and in any event it has a sloppy feel to me. The best way is to use the (non-standard) RETURNING clause: INSERT ... RETURNING id; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
