B) Using simple PostgreSQL CASE expression with aggregate function example In this example, we used a simple CASE expression to compare the rating from the film table with some literal values like G, PG, NC17, PG-13 and return the corresponding rating description. WHEN 'PG-13' THEN 'Parents Strongly Cautioned' WHEN 'PG' THEN 'Parental Guidance Suggested' The following statement uses the CASE expression to add the rating description to the output: SELECT title, A) Simple PostgreSQL CASE expression example If CASE does not find any matches, it returns the else_result in that follows the ELSE, or NULL value if the ELSE is not available. Once the result of the expression equals a value (value1, value2, etc.) in a WHEN clause, the CASE returns the corresponding result in the THEN clause. The CASE first evaluates the expression and compares the result with each value( value_1, value_2, …) in the WHEN clauses sequentially until it finds the match. PostgreSQL provides another form of the CASE expression called simple form as follows: CASE expression And we applied the SUM function to calculate the total of films for each price segment. In this example, we used the CASE expression to return 1 or 0 if the rental rate falls into each price segment. In this case, you can use the CASE expression to construct the query as follows: SELECT SUM ( CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 ENDĬASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 ENDĬASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 ENDįilm Code language: SQL (Structured Query Language) ( sql ) If the rental rate is 4.99, the film is premium.Īnd you want to know the number of films that belong to economy, mass, and premium.If the rental rate is 1.99, the film is mass.If the rental rate is 0.99, the film is economic.Suppose that you want to assign price segments to films with the following logic: B) Using CASE with an aggregate function example Note that we placed a column alias duration after the CASE expression. ORDER BY title Code language: PHP ( php ) WHEN length> 0 AND length 50 AND length 120 THEN 'Long' To apply this logic, you can use the CASE expression in the SELECT statement as follows: SELECT title, If the length is greater than 120 minutes, the film is long.If the length is greater than 50 minutes and less than or equal to 120 minutes, the film is medium.If the lengh is less than 50 minutes, the film is short.Suppose you want to label the films by their length based on the following logic: Let’s take a look at the film table from the sample database. If you omit the ELSE clause, the CASE expression returns NULL. In case all conditions evaluate to false, the CASE expression returns the result ( else_result) that follows the ELSE keyword. Also, it immediately stops evaluating the next expression. For example, if the condition_2 evaluates to true, the CASE expression returns the result_2. If a condition evaluates to true, the CASE expression returns the corresponding result that follows the condition. When a condition evaluates to false, the CASE expression evaluates the next condition from the top to bottom until it finds a condition that evaluates to true. In this syntax, each condition ( condition_1, condition_2…) is a boolean expression that returns either true or false. The following illustrates the general form of the CASE statement: CASEĮND Code language: SQL (Structured Query Language) ( sql ) The CASE expression has two forms: general and simple form. Since CASE is an expression, you can use it in any places where an expression can be used e.g., SELECT, WHERE, GROUP BY, and HAVING clause. It allows you to add if-else logic to the query to form a powerful query. The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. Just as when using a Function on a column, attempting to apply a COLLATE will bypass an index.Summary: in this tutorial, you will learn how to use the PostgreSQL CASE conditional expression to form conditional queries. With MySQL, we can do this by using the COLLATE clause and the utf8_bin collation (in my particular case):īut, we have to be careful about our database indexes. So, when I start storing Base64 values in this table, I need my WHERE clause to be able to distinguish between aaa and AAA. Which means that, with Base64, aaa and AAA are distinct values. However, when it comes to Base64, case means something. When the slug column is populated with HEX values, the case-sensitivity doesn't really matter because, with HEX, aaa and AAA are the same value. While not shown in this schema, the columns are using a utf8_general_ci collation (I'm assuming it's not showing here because that's the default collation in the database?). `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |