Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary key in the result (using a UNION). What is the most efficient way to return the entity_id's? I am assuming a simple CAST would do the trick, but this is to handle a sitewide search which requires it to be as quick as possible.

    select entityA.a_id::TEXT as entity_id from entityA
    select entityB.b_uuid::TEXT as entity_id from entityB
    select entityC.c_id::TEXT as entity_id from entityC
    select entityD.d_id::TEXT as entity_id from entityD
    select EntityE.e_id::Text as entity_id from EntityE

Any recommendations?

  • 1
    Casting to text should not add too much overhead - you can easily try it by comparing the execution times on the same subset (one with UUIDs, one with bigints) with and without casting. – dezso Mar 29 '16 at 16:35

Casting to text should not add much overhead, however you may want to consider using UNION ALL instead of UNION if you're looking for performance (UNION attempts to filtering duplicates).

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.