UUID vs serial and currval('sequence_id')
Hi,
When doing an insert with a serial primary key we can refer to
currval('sequence_name') in subsequent inserts and we also return it for
later processing.
Example:
CREATE TABLE contact (
contactid serial not null primary key, -- creates sequence
'contact_contactid_seq'
firstname text not null,
lastname text
);
CREATE TABLE contactinterests(
contactid int not null references contact(contactid),
interest text
);
-- insert statement as single transaction
INSERT INTO contact(
firstname, lastname)
VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
VALUES (currval('contact_contactid_seq'),'Fishing');
--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?
Thanks
Robert
On Mon, May 2, 2022 at 3:33 PM Robert Stanford <rstanford@gmail.com> 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.
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 );
Robert
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 contactidwith 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.