This obviously results:

SELECT *, uuid_generate_v4() FROM fruits;

apple      |f6b9a8a5-31c9-4917-9649-f366b7c12492
orange     |a55da08f-95a0-4f33-b536-866a3eae71e9

I know I could do

SELECT * FROM fruits LEFT OUTER JOIN uuid_generate_v4() ON TRUE;

But is there some nicer way to assign same uuid? I'm mostly interested on conceptual level, the uuid() function is just example.

  • You - by the way - don't need an outer join, you can do with fruits JOIN uuid_generate_v4() ON TRUE. Which in turn can be simplified to fruits CROSS JOIN uuid_generate_v4() – Lennart Jun 13 '18 at 13:26

So you want to make sure that uuid_generate_v4() is only called once.

Your solution is essentially doing that. Other solutions only vary in the syntax being used.

You could use a common table expression to somehow express that in your query:

with data (id) as (
   select uuid_generate_v4()
FROM fruits 
  cross join data;

As the CTE only returns a single row, the cross join won't increase the number of rows in the complete result. Alternatively you could use an "implicit" cross join to make it shorter:

with data (id) as (
   select uuid_generate_v4()
FROM fruits, data;

Or you could remove the CTE and use the function call in a derived table:

FROM fruits, (select uuid_generate_v4() as id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.