SUPERSETEQ Operator

Description

The SUPERSETEQ operator returns TRUE(1) when a second operand is a subset of a first operand; that is, the first one is identical to or larger than the second one. Note that SUPERSETEQ is not supported if an operand is LIST type.

Syntax

collection_operand SUPERSETEQ collection_operand

Example

--selecting rows when the first operand is a superset of the second operand

SELECT id, name, address, zip_code FROM contain_tbl WHERE address SUPERSETEQ {'country','state','city'};

 

           id  name                  address               zip_code

===============================================================================

            3  'Jones     '          {'city', 'country', 'state'}  {1, 2, 3, 4}

            4  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            5  'Kim       '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            6  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 5}

            7  'Brown     '          {'city', 'country', 'state', 'street'}  {}

 

--SUPERSETEQ operator cannot be used for comparison between LIST and LIST type values

SELECT id, name, address, zip_code FROM contain_tbl WHERE zip_code SUPERSETEQ {1,2,3};

 

ERROR: ' superseteq ' operator is not defined on types sequence and sequence.

 

--Comparing operands with a SUPERSETEQ operator after casting LIST type as SET type

SELECT id, name, address, zip_code FROM contain_tbl WHERE zip_code SUPERSETEQ (CAST ({1,2,3} AS SET));

           id  name                  address               zip_code

===============================================================================

            1  'Kim       '          {'country', 'state'}  {1, 2, 3}

            3  'Jones     '          {'city', 'country', 'state'}  {1, 2, 3, 4} 

            4  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            5  'Kim       '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 4}

            6  'Smith     '          {'city', 'country', 'state', 'street'}  {1, 2, 3, 5}