Thread: 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
);
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(
INSERT INTO contact(
firstname, lastname)
VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
VALUES (currval('contact_contactid_seq'),'Fishing');
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')
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 contactidINSERT 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
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 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 (
)
INSERT INTO contactinterests(
contactuuid, interest)
VALUES (
(select thisuuid from contactuuid ),'Fishing')
returning (select thisuuid from contactuuid );
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.