Re: UUID vs serial and currval('sequence_id')

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, May 2, 2022 at 3:33 PM Robert Stanford <rstanford@xxxxxxxxx> wrote:

--insert statement as single transaction returning contactid

INSERT INTO contact(
    firstname, lastname)
  VALUES('John', 'Smith');
INSERT INTO contactinterests(
    contactid, interest)
  VALUES (currval('contact_contactid_seq'),'Fishing') 

returning currval('contact_contactid_seq');

Which is very nice as it gives us back the contactid.

Is it possible to get similar functionality using gen_random_uuid() or uuid-ossp?

You basically have to use "INSERT ... RETURNING" or variables.  Which/how depends on the language you are writing in.  Pure SQL without client involvement requires that you use chained CTEs of INSERT...RETURNING (or I suppose you could leverage set_config(), haven't tried that way myself).  In pl/pgsql you can also use variables, and the same goes for psql - though that requires client involvement and so isn't generally that great a choice.

David J.