The WHERE clause cannot be used to filter data that contains null values. To filter data that contains null values, install the LNNVL function in a PolarDB for PostgreSQL(Compatible with Oracle) cluster.

Install the LNNVL function

The LNNVL function is supported by PolarDB for PostgreSQL(Compatible with Oracle) as a plug-in. Before you use the LNNVL function, run the following commands to install the plug-in:

SET client_min_messages TO 'ERROR';
CREATE EXTENSION if not exists polar_lnnvl;
RESET client_min_messages;

Syntax

lnnvl(condition)
ParameterDescription
conditionThe query condition for the LNNVL function.

Description

Note The LNNVL function is supported only in the WHERE clause.
A condition is specified as the parameter in the LNNVL function.
  • If some data in the table matches the condition, all data that does not match the condition is returned. Null values can be returned.
  • If no data in the table matches the condition, all data is returned. Null values can be returned.
  • If only null values in the table match the condition, all data except null values is returned.

For example, a table contains the following two rows of data:

name | id
------------
 a   | 2
 b   | null

The following table lists the results that are returned by the LNNVL function with different conditions.

ConditionWhether a non-null or null value matches the conditionLNNVL return value
lnnvl(id=1)NoRows a and b
lnnvl(id=2)YesRow b
lnnvl(id>2)NoRows a and b
lnnvl(id<2)NoRows a and b
lnnvl(id is null)YesRow a

Examples

Run the following commands to create a table named account and insert test data into the table:

create table account(name varchar2(20),year number);
insert into account values('peter2001',2001);
insert into account values('peter2002',2002);
insert into account values('peter2003',2003);
insert into account values('peter2004',2004);
insert into account values('peter2005',2005);
insert into account values('peter2006',2006);
insert into account values('peter2007',null);
  • If the condition is year<2003, the LNNVL function returns the rows where the value in the year column is greater than or equal to 2003 or is null.

    Execute the following statement:

    select * from account where lnnvl(year<2003);

    The following result is returned:

       name    | year 
    -----------+------
     peter2003 | 2003
     peter2004 | 2004
     peter2005 | 2005
     peter2006 | 2006
     peter2007 | null
  • If the condition is year is not null, the LNNVL function returns the rows where the value in the year column is null.

    Execute the following statement:

    select * from account where lnnvl(year is not null);

    The following result is returned:

       name    | year 
    -----------+------
     peter2007 | null
  • If the condition is year is null, the LNNVL function returns the rows where the value in the year column is not null.

    Execute the following statement:

    select * from account where lnnvl(year is null);

    The following result is returned:

       name    | year 
    -----------+------
     peter2001 | 2001
     peter2002 | 2002
     peter2003 | 2003
     peter2004 | 2004
     peter2005 | 2005
     peter2006 | 2006
  • If the condition is year=2008, the LNNVL function returns the rows where the value in the year column is not 2008.

    Execute the following statement:

    select * from account where lnnvl(year=2008);

    The following result is returned:

       name    | year 
    -----------+------
     peter2001 | 2001
     peter2002 | 2002
     peter2003 | 2003
     peter2004 | 2004
     peter2005 | 2005
     peter2006 | 2006
     peter2007 | null
  • If the condition is year!=2008 , the LNNVL function returns the rows where the value in the year column is 2008 or null.

    Execute the following statement:

    select * from account where lnnvl(year! =2008);

    The following result is returned:

       name    | year 
    -----------+------
     peter2007 | null