professorgift.blogg.se - Postgresql if then else

Postgresql if then else how to#
Postgresql if then else code#

įourth, let’s remove the female member: DELETE FROM In this case, it returns 200%, which is correct. Then the total of male members is divided by the total of female members to return the ratio. The same logic is also applied for calculating the total number of female members. If the gender is 1, the CASE expression returns 1, otherwise it returns 0 the SUM function is used to calculate total of male members. To calculate the total number of male members, we use the SUM function and CASE expression.

Postgresql if then else code#

Members Code language: SQL (Structured Query Language) ( sql ) Third, if we want to calculate the ratio between male and female members, we use the following query: SELECT ( 'Bush', 'Lily', 2) Code language: SQL (Structured Query Language) ( sql ) Second, we insert some rows for testing: INSERT INTO members ( Gender SMALLINT NOT NULL - 1: male, 2 female Let’s take a look at the following example.įirst, we create a new table named members: CREATE TABLE members ( Use NULLIF to prevent division-by-zero errorĪnother great example of using the NULLIF function is to prevent division-by-zero error.

Second, the COALESCE function checks if the first argument, which is provided by the NULLIF function, if it is null, then it returns the first 40 characters of the body otherwise it returns the excerpt in case the excerpt is not null.

The result of the NULLIF function is used by the COALESCE function.

First, the NULLIF function returns a null value if the excerpt is empty, otherwise it returns the excerpt.

Let’s examine the expression in more detail: This is why we need to use the NULLIF function: SELECT id,Ĭode language: SQL (Structured Query Language) ( sql ) Unfortunately, there is mix between null value and ” (empty) in the excerpt column. To substitute this null value, we can use the COALESCE function as follows: SELECT id, We see the null value in the excerpt column. Posts Code language: SQL (Structured Query Language) ( sql ) We can simply use the following query to get all rows in the posts table. In case the excerpt is not provided, we use the first 40 characters of the post body. Third, our goal is to display the posts overview page that shows title and excerpt of each posts. ( 'test post 3', null, 'test post body 3') Code language: SQL (Structured Query Language) ( sql )

( 'test post 2', '', 'test post body 2'), ( 'test post 1', 'test post excerpt 1', 'test post body 1'), Second, we insert some sample data into the posts table. ) Code language: SQL (Structured Query Language) ( sql ) Let’s take a look at an example of using the NULLIF function.įirst, we create a table named posts as follows: CREATE TABLE posts (Ĭreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, See the following examples: SELECT NULLIF ( 1, 1) - return NULL SELECT NULLIF ( 1, 0) - return 1 SELECT NULLIF ( 'A', 'B') - return A Code language: SQL (Structured Query Language) ( sql ) PostgreSQL NULLIF function example The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.

The following illustrates the syntax of the NULLIF function: NULLIF(argument_1,argument_2) Code language: SQL (Structured Query Language) ( sql ) The NULLIF function is one of the most common conditional expressions provided by PostgreSQL.

We will show you some examples of using the NULLIF function.

Postgresql if then else how to#

Summary: this tutorial shows you how to use PostgreSQL NULLIF function to handle null values.