Conditional CTE in PostgreSQLPostgreSQL “DESCRIBE TABLE”Show tables in PostgreSQLCreating a copy of a database in PostgreSQLInsert, on duplicate update in PostgreSQL?Save PL/pgSQL output from PostgreSQL to a CSV fileHow can I drop all the tables in a PostgreSQL database?How to start PostgreSQL server on Mac OS X?How to exit from PostgreSQL command line utility: psqlHow to change PostgreSQL user password?Which version of PostgreSQL am I running?
When to remove insignificant variables?
Find the C-factor of a vote
How does the spell Remove Curse interact with a Sword of Vengeance?
Is it damaging to turn off a small fridge for two days every week?
What reason would an alien civilization have for building a Dyson Sphere (or Swarm) if cheap Nuclear fusion is available?
Why do even high-end cameras often still include normal (non-cross-type) AF sensors?
What can I do with a research project that is my university’s intellectual property?
How does a pilot select the correct ILS when the airport has parallel runways?
Can Ogre clerics use Purify Food and Drink on humanoid characters?
What does it mean to "control target player"?
Why tighten down in a criss-cross pattern?
How much will studying magic in an academy cost?
How is hair tissue mineral analysis performed?
Why is it recommended to mix yogurt starter with a small amount of milk before adding to the entire batch?
Can humans ever directly see a few photons at a time? Can a human see a single photon?
Should I prioritize my 401k over my student loans?
Why do all the teams that I have worked with always finish a sprint without completion of all the stories?
Suggested order for Amazon Prime Doctor Who series
How would modern naval warfare have to have developed differently for battleships to still be relevant in the 21st century?
Is there a maximum distance from a planet that a moon can orbit?
Drawing people along with x and y axis
Count All Possible Unique Combinations of Letters in a Word
Is a single radon-daughter atom in air a solid?
Who are the remaining King/Queenslayers?
Conditional CTE in PostgreSQL
PostgreSQL “DESCRIBE TABLE”Show tables in PostgreSQLCreating a copy of a database in PostgreSQLInsert, on duplicate update in PostgreSQL?Save PL/pgSQL output from PostgreSQL to a CSV fileHow can I drop all the tables in a PostgreSQL database?How to start PostgreSQL server on Mac OS X?How to exit from PostgreSQL command line utility: psqlHow to change PostgreSQL user password?Which version of PostgreSQL am I running?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I'm trying to write a dynamic query. I've tried to simplify this as best as I can. @Condition and @Filter are the dynamic inputs of the query.
There are two cases:
- If
@ConditionisTRUE, then do not filter thedataon the presence of anymetadata - Otherwise, find the rows of
metadatathat match a certain filer, and filter thedataon those.
Pseudo code a little bit like this:
WITH selected_metadata AS (
IF (@Condition = TRUE)
-- Do not filter on metadata
SELECT NULL;
ELSE
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter
)
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
selected_metadata IS NULL
OR
-- Filter on metadata
data.metadataid IN (selected_metadata)
)
...more filters
I think there might be easier ways to do this without a CTE, but because of how the query looks in real life, I think I need to split it up to minimize complexity.
sql postgresql postgresql-9.6
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
asked Mar 25 at 8:41
JoelJoel
4,10254175
add a comment |
I'm trying to write a dynamic query. I've tried to simplify this as best as I can. @Condition and @Filter are the dynamic inputs of the query.
There are two cases:
- If
@ConditionisTRUE, then do not filter thedataon the presence of anymetadata - Otherwise, find the rows of
metadatathat match a certain filer, and filter thedataon those.
Pseudo code a little bit like this:
WITH selected_metadata AS (
IF (@Condition = TRUE)
-- Do not filter on metadata
SELECT NULL;
ELSE
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter
)
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
selected_metadata IS NULL
OR
-- Filter on metadata
data.metadataid IN (selected_metadata)
)
...more filters
I think there might be easier ways to do this without a CTE, but because of how the query looks in real life, I think I need to split it up to minimize complexity.
sql postgresql postgresql-9.6
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
asked Mar 25 at 8:41
JoelJoel
4,10254175
add a comment |
I'm trying to write a dynamic query. I've tried to simplify this as best as I can. @Condition and @Filter are the dynamic inputs of the query.
There are two cases:
- If
@ConditionisTRUE, then do not filter thedataon the presence of anymetadata - Otherwise, find the rows of
metadatathat match a certain filer, and filter thedataon those.
Pseudo code a little bit like this:
WITH selected_metadata AS (
IF (@Condition = TRUE)
-- Do not filter on metadata
SELECT NULL;
ELSE
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter
)
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
selected_metadata IS NULL
OR
-- Filter on metadata
data.metadataid IN (selected_metadata)
)
...more filters
I think there might be easier ways to do this without a CTE, but because of how the query looks in real life, I think I need to split it up to minimize complexity.
sql postgresql postgresql-9.6
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
asked Mar 25 at 8:41
JoelJoel
4,10254175
I'm trying to write a dynamic query. I've tried to simplify this as best as I can. @Condition and @Filter are the dynamic inputs of the query.
There are two cases:
- If
@ConditionisTRUE, then do not filter thedataon the presence of anymetadata - Otherwise, find the rows of
metadatathat match a certain filer, and filter thedataon those.
Pseudo code a little bit like this:
WITH selected_metadata AS (
IF (@Condition = TRUE)
-- Do not filter on metadata
SELECT NULL;
ELSE
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter
)
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
selected_metadata IS NULL
OR
-- Filter on metadata
data.metadataid IN (selected_metadata)
)
...more filters
I think there might be easier ways to do this without a CTE, but because of how the query looks in real life, I think I need to split it up to minimize complexity.
sql postgresql postgresql-9.6
sql postgresql postgresql-9.6
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
asked Mar 25 at 8:41
JoelJoel
4,10254175
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
asked Mar 25 at 8:41
JoelJoel
4,10254175
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
edited Mar 25 at 8:52
a_horse_with_no_name
318k49487592
318k49487592
asked Mar 25 at 8:41
JoelJoel
4,10254175
asked Mar 25 at 8:41
JoelJoel
4,10254175
asked Mar 25 at 8:41
JoelJoel
4,10254175
4,10254175
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I think you can try to set those condition in where instead of CTE directly.
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
@Condition = TRUE
OR
-- Filter on metadata
@Condition = FALSE AND data.metadataid IN (SELECT ID FROM selected_metadata AND field = @Filter)
)
...more filters
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
add a comment |
Try something like this:
WITH selected_metadata AS (
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter OR @Condition <> TRUE
)
SELECT ...
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
In this case there is no way for me to know ifselected_metadatais empty because@ConditionwasFALSEor because there were no matching rows. If@ConditionisFALSE, I want all the rows fromdata
– Joel
Mar 25 at 9:11
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55333961%2fconditional-cte-in-postgresql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you can try to set those condition in where instead of CTE directly.
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
@Condition = TRUE
OR
-- Filter on metadata
@Condition = FALSE AND data.metadataid IN (SELECT ID FROM selected_metadata AND field = @Filter)
)
...more filters
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
add a comment |
I think you can try to set those condition in where instead of CTE directly.
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
@Condition = TRUE
OR
-- Filter on metadata
@Condition = FALSE AND data.metadataid IN (SELECT ID FROM selected_metadata AND field = @Filter)
)
...more filters
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
add a comment |
I think you can try to set those condition in where instead of CTE directly.
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
@Condition = TRUE
OR
-- Filter on metadata
@Condition = FALSE AND data.metadataid IN (SELECT ID FROM selected_metadata AND field = @Filter)
)
...more filters
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
I think you can try to set those condition in where instead of CTE directly.
SELECT
*
FROM data
WHERE
(
-- Ignore the metadata filter
@Condition = TRUE
OR
-- Filter on metadata
@Condition = FALSE AND data.metadataid IN (SELECT ID FROM selected_metadata AND field = @Filter)
)
...more filters
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
edited Mar 25 at 8:50
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
answered Mar 25 at 8:44
D-ShihD-Shih
28.9k61535
28.9k61535
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
add a comment |
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
Yes, this is basically what I want to do, but would it be possible to do via a CTE instead? I think the execution planner can make more consistent desisions if I don't do it as a sub select (not sure though)
– Joel
Mar 25 at 9:13
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@joel . . . I don't think a subquery versus CTE is going to affect how Postgres optimizes the query.
– Gordon Linoff
Mar 25 at 10:58
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
@GordonLinoff Ok. I'm doing some tests right now with a sub query instead, hoping it'll work fine!
– Joel
Mar 25 at 13:34
add a comment |
Try something like this:
WITH selected_metadata AS (
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter OR @Condition <> TRUE
)
SELECT ...
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
In this case there is no way for me to know ifselected_metadatais empty because@ConditionwasFALSEor because there were no matching rows. If@ConditionisFALSE, I want all the rows fromdata
– Joel
Mar 25 at 9:11
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
add a comment |
Try something like this:
WITH selected_metadata AS (
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter OR @Condition <> TRUE
)
SELECT ...
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
In this case there is no way for me to know ifselected_metadatais empty because@ConditionwasFALSEor because there were no matching rows. If@ConditionisFALSE, I want all the rows fromdata
– Joel
Mar 25 at 9:11
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
add a comment |
Try something like this:
WITH selected_metadata AS (
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter OR @Condition <> TRUE
)
SELECT ...
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
Try something like this:
WITH selected_metadata AS (
-- Find the matching metadata rows
SELECT id FROM metadata WHERE field = @Filter OR @Condition <> TRUE
)
SELECT ...
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
edited Mar 25 at 15:46
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
answered Mar 25 at 8:58
Usagi MiyamotoUsagi Miyamoto
4,93711325
4,93711325
In this case there is no way for me to know ifselected_metadatais empty because@ConditionwasFALSEor because there were no matching rows. If@ConditionisFALSE, I want all the rows fromdata
– Joel
Mar 25 at 9:11
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
add a comment |
In this case there is no way for me to know ifselected_metadatais empty because@ConditionwasFALSEor because there were no matching rows. If@ConditionisFALSE, I want all the rows fromdata
– Joel
Mar 25 at 9:11
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
In this case there is no way for me to know if
selected_metadata is empty because @Condition was FALSE or because there were no matching rows. If @Condition is FALSE, I want all the rows from data– Joel
Mar 25 at 9:11
In this case there is no way for me to know if
selected_metadata is empty because @Condition was FALSE or because there were no matching rows. If @Condition is FALSE, I want all the rows from data– Joel
Mar 25 at 9:11
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
My bad, corrected that...
– Usagi Miyamoto
Mar 25 at 15:46
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55333961%2fconditional-cte-in-postgresql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
