UUID vs serial and currval('sequence_id')

From: Robert Stanford <rstanford(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: UUID vs serial and currval('sequence_id')
Date: 2022-05-02 22:33:31
Message-ID: CAC1FqCE54E6_OHkAmHq6MO_=u87-0ON875A52=Ji9WLwieSTUw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-05-02 22:39:05 Re: UUID vs serial and currval('sequence_id')
Previous Message Rich Shepard 2022-05-02 21:31:16 Re: External psql editor