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

On Mon, May 2, 2022 at 4:24 PM Robert Stanford <rstanford(at)gmail(dot)com> wrote:

> On Tue, 3 May 2022 at 08:39, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)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.