![]() Sometimes, it is called a CASE WHEN expression or any of the others I’ve mentioned above. Strictly speaking, it is called a CASE statement in SQL. This post has presented various examples to explain the usage of the case statement in PostgreSQL.CASE, CASE WHEN, CASE WHEN THEN, or CASE WHEN THEN ELSE? It can be used with any statement or clause where an expression can be used, such as with a WHERE clause, with the SELECT statement, etc. We can use the WHEN-THEN case with the CASE statement to create or formulate a query/expression. Postgres supports two forms of the CASE statement: A Searched CASE and a simple CASE. The CASE statement is one of the conditional expressions that is used to create conditional queries. This way, you can use any aggregate function with the CASE statement to achieve different functionalities. In the below-provided query, we will use the COUNT() function to count the number of “junior”, and “senior” authors: SELECT Postgres allows us to use the simple and searched CASE statements with the aggregate functions. This is how the Simple CASE Statement works in PostgreSQL.Įxample 3: How to Use the CASE Statement With Aggregate Functions in Postgres? In the above snippet, the CASE statement is used to add the gender description to the output: The following snippet demonstrates the working of the simple CASE statement: SELECT author_name, author_exp, The output shows the usage of the CASE statement in PostgreSQL.Įxample 2: How Does the Simple CASE Statement Work in Postgres? ![]() WHEN author_exp >= 0 AND author_exp 1 AND author_exp 3 AND author_exp 5 THEN 'Expert' In the following statement, we will use the CASE expression to check if the author is a “freshman” “junior”, “senior”, or “expert”: SELECT author_name, author_exp, In this example, we will apply the CASE statement on the “author_info” table, whose data is shown in the following snippet: SELECT * For instance, if “condition_1” satisfies the “search_expression” then “result_1” will be retrieved.Įxample 1: How Does the Searched CASE Statement Work in Postgres? When a condition retrieves a “TRUE” value then the result associated with that condition will be retrieved. The “Search_expression” represents an expression to be evaluated against the expression specified in each “WHEN” case. The below snippet illustrates the syntax of a simple CASE statement: CASE search_expression If all the conditions/expressions retrieve “FALSE” then the result associated with the “ELSE” block will be executed. This process will continue until an expression retrieves true. However, if a condition retrieves a “FALSE” value, then the CASE expression will evaluate the next condition. Once the CASE statement encounters that an expression retrieves a TRUE value, then immediately, it will stop evaluating the remaining expressions. For instance, when “condition_1” becomes true, then “result_1” will be retrieved. The CASE statement will execute each condition specified within the CASE statement from top-to-bottom. ![]() The conditions, such as codition_1, condition_2, …, condition_n, are the boolean conditions that will retrieve either true or false. ![]() Let’s comprehend the above syntax line-by-line: The below snippet depicts the syntax of a searched CASE statement: CASE Postgres supports two forms of the CASE statement: A Searched CASE and a Simple CASE. What is CASE Statement and How to Write it in Postgres?Īs stated earlier, the CASE statement is a conditional expression, so it can be used with any statement or clause where an expression can be used, such as a WHERE clause, SELECT statement, etc. This post illustrates several use cases of the CASE statement in PostgreSQL via practical examples. with the CASE statement to create or formulate a query/expression. PostgreSQL allows us to use the WHEN-THEN case, if-else statements, etc. These statements include if, if-else, case, etc. Conditional statements are the core concepts in any programming paradigm.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |