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

[Robert Stanford <rstanford@xxxxxxxxx>]

On Tue, 3 May 2022 at 08:39, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

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.

Thanks, so  I can do:

alter table contact add column contactuuid uuid
alter table contactinterests add column contactuuid uuid
alter table contactinterests drop column contactid

with thisuuid as (
    SELECT gen_random_uuid() as thisuuid
),
 contactuuid as(
    INSERT INTO contact(
         contactuuid,firstname, lastname)
    VALUES(

        (select thisuuid  from thisuuid ),'John', 'Smith') returning (select thisuuid  from thisuuid )
)
INSERT INTO contactinterests(
    contactuuid, interest)
  VALUES (

    (select thisuuid  from contactuuid ),'Fishing')
          returning (select thisuuid  from contactuuid );

 Robert