I have a Postgres SELECT
statement with these expressions:
,CASE WHEN (rtp.team_id = rtp.sub_team_id) THEN 'testing' ELSE TRIM(rtd2.team_name) END AS testing_testing ,CASE WHEN (rtp.team_id = rtp.sub_team_id) THEN 'test example' ELSE TRIM(rtd2.normal_data) END AS test_response ,CASE WHEN (rtp.team_id = rtp.sub_team_id) THEN 'test example #2' ELSE TRIM(rtd2.normal_data_2) END AS another_example
In my particular query there are 5 fields whose output depends on whether rtp.team_id = rtp.sub_team_id
evaluates true. I'm repeating CASE
statements with the same condition over and over.
Is there any way I can combine these CASE
expressions to toggle the output of multiple columns in one shot?
When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause.
Yes, you can evaluate different columns in the CASE statement.
Yes the number of columns will - indirectly - influence the performance. The data in the columns will also affect the speed.
If you specify a list of columns, you need to place a comma ( , ) between two columns to separate them. If you want to select data from all the columns of the table, you can use an asterisk ( * ) shorthand instead of specifying all the column names.
LEFT JOIN
a single row of valuesYou could LEFT JOIN
a row of values using the condition (thereby evaluating it once). Then you can add fallback values per column with COALESCE()
.
This syntax variant is shorter and slightly faster with multiple values - especially interesting for an expensive / lengthy condition:
SELECT COALESCE(x.txt1, trim(r2.team_name)) AS testing_testing , COALESCE(x.txt2, trim(r2.normal_data)) AS test_response , COALESCE(x.txt3, trim(r2.normal_data_2)) AS another_example FROM rtp JOIN rtd2 r2 ON <unknown condition> -- missing context in question LEFT JOIN ( SELECT 'testing'::text AS txt1 , 'test example'::text AS txt2 , 'test example #2'::text AS txt3 ) x ON rtp.team_id = rtp.sub_team_id;
Since the derived table x
consists of a single row, joining without further conditions is fine.
Explicit type casts are necessary in the subquery. I use text
in the example (which is the default for string literals anyway). Use your actual data types. The syntax shortcut value::type
is Postgres-specific, use cast(value AS type)
for standard SQL.
If the condition is not TRUE
, all values in x
are NULL, and COALESCE
kicks in.
Or, since all candidate values come from table rtd2
in your particular case, LEFT JOIN
to rtd2
using the original CASE
condition and CROSS JOIN
to a row with default values:
SELECT COALESCE(trim(r2.team_name), x.txt1) AS testing_testing , COALESCE(trim(r2.normal_data), x.txt2) AS test_response , COALESCE(trim(r2.normal_data_2), x.txt3) AS another_example FROM rtp LEFT JOIN rtd2 r2 ON <unknown condition> -- missing context in question AND rtp.team_id = rtp.sub_team_id CROSS JOIN ( SELECT 'testing'::text AS txt1 , 'test example'::text AS txt2 , 'test example #2'::text AS txt3 ) x;
It depends on the join conditions and the rest of the query.
If your various columns share the same data type, you can use an array in a subquery and expand it in the outer SELECT
:
SELECT x.combo[1], x.combo[2], x.combo[3] FROM ( SELECT CASE WHEN rtp.team_id = rtp.sub_team_id THEN '{test1,test2,test3}'::text[] ELSE ARRAY[trim(r2.team_name) , trim(r2.normal_data) , trim(r2.normal_data_2)] END AS combo FROM rtp JOIN rtd2 r2 ON <unknown condition> ) x;
It gets more complicated if the columns don't share the same data type. You can either cast them all to text
(and optionally convert back in the outer SELECT
), or you can ...
You can use a custom composite type (row type) to hold values of various types and simply *-expand it in the outer SELECT
. Say we have three columns: text
, integer
and date
. For repeated use, create a custom composite type:
CREATE TYPE my_type (t1 text, t2 int, t3 date);
Or if the type of an existing table matches, you can just use the table name as composite type.
Or if you only need the type temporarily, you can create a TEMPORARY TABLE
, which registers a temporary type for the duration of your session:
CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date);
You could even do this for a single transaction:
CREATE TEMP TABLE my_type (t1 text, t2 int, t3 date) ON COMMIT DROP;
Then you can use this query:
SELECT (x.combo).* -- parenthesis required FROM ( SELECT CASE WHEN rtp.team_id = rtp.sub_team_id THEN ('test', 3, now()::date)::my_type -- example values ELSE (r2.team_name , r2.int_col , r2.date_col)::my_type END AS combo FROM rtp JOIN rtd2 r2 ON <unknown condition> ) x;
Or even just (same as above, simpler, shorter, maybe less easy to understand):
SELECT (CASE WHEN rtp.team_id = rtp.sub_team_id THEN ('test', 3, now()::date)::my_type ELSE (r2.team_name, r2.int_col, r2.date_col)::my_type END).* FROM rtp JOIN rtd2 r2 ON <unknown condition>;
The CASE
expression is evaluated once for every column this way. If the evaluation is not trivial, the other variant with a subquery will be faster.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With