Sfrgttk

Paid for article while in US on F-1 visa?

Can you really stack all of this on an Opportunity Attack?

I'm flying to France today and my passport expires in less than 2 months

Cross compiling for RPi - error while loading shared libraries

Why is consensus so controversial in Britain?

Languages that we cannot (dis)prove to be Context-Free

Are the number of citations and number of published articles the most important criteria for a tenure promotion?

NMaximize is not converging to a solution

Is it unprofessional to ask if a job posting on GlassDoor is real?

Does detail obscure or enhance action?

How is the claim "I am in New York only if I am in America" the same as "If I am in New York, then I am in America?

dbcc cleantable batch size explanation

Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?

Which country benefited the most from UN Security Council vetoes?

How old can references or sources in a thesis be?

Arrow those variables!

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

Why can't we play rap on piano?

What is a clear way to write a bar that has an extra beat?

Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?

Decision tree nodes overlapping with Tikz

Client team has low performances and low technical skills: we always fix their work and now they stop collaborate with us. How to solve?

How is it possible to have an ability score that is less than 3?

Approximately how much travel time was saved by the opening of the Suez Canal in 1869?

Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key

Efficiently joining two irregular series representing changesUsing COLLATE with UNIONTable with both primary key and unique keyPrimary key in resulting relation from union operation in relational algebraUsing default values in primary keyDuplicate row with Primary Key in PostgreSQLDoes Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficientEmpty table being returned with plpgsql and EXECUTE USINGUsing SELECT within to_tsvector call in CREATE INDEXLook for key in jsonb array, LIKE with @>

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

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

    union

    select entityB.b_uuid::TEXT as entity_id from entityB

    union

    select entityC.c_id::TEXT as entity_id from entityC

    union

    select entityD.d_id::TEXT as entity_id from entityD

    union

    select EntityE.e_id::Text as entity_id from EntityE

Any recommendations?

postgresql union uuid

share|improve this question

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

1

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

    union

    select entityB.b_uuid::TEXT as entity_id from entityB

    union

    select entityC.c_id::TEXT as entity_id from entityC

    union

    select entityD.d_id::TEXT as entity_id from entityD

    union

    select EntityE.e_id::Text as entity_id from EntityE

Any recommendations?

postgresql union uuid

share|improve this question

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

bumped to the homepage by Community♦ 5 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  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
  

  
  
  
  

add a comment | 

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

    union

    select entityB.b_uuid::TEXT as entity_id from entityB

    union

    select entityC.c_id::TEXT as entity_id from entityC

    union

    select entityD.d_id::TEXT as entity_id from entityD

    union

    select EntityE.e_id::Text as entity_id from EntityE

Any recommendations?

postgresql union uuid

share|improve this question

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

    select entityA.a_id::TEXT as entity_id from entityA

    union

    select entityB.b_uuid::TEXT as entity_id from entityB

    union

    select entityC.c_id::TEXT as entity_id from entityC

    union

    select entityD.d_id::TEXT as entity_id from entityD

    union

    select EntityE.e_id::Text as entity_id from EntityE

share|improve this question

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

share|improve this question

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

edited Mar 30 '16 at 6:41

a_horse_with_no_name

41.4k779116

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

asked Mar 29 '16 at 15:50

Laurie H.Laurie H.

62

1 Answer
1

active

oldest

votes

0

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).

share|improve this answer

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f133696%2fstarted-using-uuids-as-primary-key-would-like-to-execute-union-of-entities-wit%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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).

share|improve this answer

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819

add a comment | 

0

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).

share|improve this answer

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819

add a comment | 

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).

share|improve this answer

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819

share|improve this answer

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819

answered Mar 30 '16 at 6:32

Ziggy Crueltyfree ZeitgeisterZiggy Crueltyfree Zeitgeister

4,2751819