Re: UUID vs serial and currval('sequence_id') - Mailing list pgsql-general

From David G. Johnston
Subject Re: UUID vs serial and currval('sequence_id')
Date
Msg-id CAKFQuwaNO9XBtDPYgrg1ntQd---QuO2fQEodOKVe5cFuqizdOA@mail.gmail.com
Whole thread Raw
In response to Re: UUID vs serial and currval('sequence_id')  (Robert Stanford <rstanford@gmail.com>)
List pgsql-general
On Mon, May 2, 2022 at 4:24 PM Robert Stanford <rstanford@gmail.com> wrote:
On Tue, 3 May 2022 at 08:39, David G. Johnston <david.g.johnston@gmail.com> 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 );


It works but "returning contactuuid" is considerably easier to understand and probably cheaper to execute.

If you are going to pre-compute the uuid the returning clause becomes pointless though, as your example demonstrates - you never actually use the returned value.

I suggest avoiding naming the CTE query and the column(s) it produces the same thing.

David J.

David J.

pgsql-general by date:

Previous
From: Robert Stanford
Date:
Subject: Re: UUID vs serial and currval('sequence_id')
Next
From: Steve Litt
Date:
Subject: Re: External psql editor