Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.5 (latest) v2.2 (stable) v2.1 (earlier version) v2.0 (earlier version) v1.3 (earlier version)
  • YUGABYTEDB CORE
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore distributed SQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
        • Scala
    • Explore features
      • YSQL vs PostgreSQL
        • Schemas and Tables
        • Data Types
      • Fault tolerance
      • Horizontal Scalability
        • Scaling Transactions
        • Sharding Data
      • Transactions
        • Distributed Transactions
        • Isolation Levels
        • Explicit Locking
      • JSON Support
      • Multi-Region Deployments
        • Sync replication (3+ regions)
        • Async Replication (2+ regions)
        • Row-Level Geo-Partitioning
      • Follower reads
      • Colocated tables
      • Change data capture (CDC)
      • Observability
        • Prometheus Integration
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
        • 9. TTL for data expiration
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Build GraphQL apps
        • Hasura
        • Prisma
      • Real-world examples
        • E-Commerce app
        • IoT fleet management
        • Retail Analytics
      • Explore sample apps
      • Best practices
    • Migrate
      • Migration process overview
      • Migrate from PostgreSQL
        • Convert a PostgreSQL schema
        • Migrate a PostgreSQL application
        • Export PostgreSQL data
        • Prepare a cluster
        • Import PostgreSQL data
        • Verify Migration
    • Deploy
      • Deployment checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Single-zone
          • Open Source
          • Amazon EKS
          • Google Kubernetes Engine
          • Azure Kubernetes Service
        • Multi-zone
          • Amazon EKS
          • Google Kubernetes Engine
        • Multi-cluster
          • Google Kubernetes Engine
        • Best practices
        • Connect clients
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Multi-DC deployments
        • Three+ data center (3DC)
        • Two data center (2DC)
        • Read replica clusters
      • Change data capture (CDC)
        • CDC to Kafka
    • Benchmark
      • TPC-C
      • sysbench
      • YCSB
      • Key-value workload
      • Large datasets
      • Scalability
        • Scaling queries
      • Resilience
        • Jepsen testing
    • Secure
      • Security checklist
      • Enable Authentication
        • Enable User Authentication
        • Configure ysql_hba_conf_csv
      • Authentication Methods
        • Password Authentication
        • LDAP Authentication
        • Host-Based Authentication
        • Trust Authentication
      • Role-Based Access Control
        • Overview
        • Manage Users and Roles
        • Grant Privileges
        • Row-Level Security (RLS)
        • Column-Level Security
      • Encryption in transit
        • Create client certificates
        • Create server certificates
        • Enable server-to-server encryption
        • Enable client-to-server encryption
        • Connect to clusters
      • Encryption at rest
      • Column-Level Encryption
      • Audit Logging
        • Configure Audit Logging
        • Session-Level Audit Logging
        • Object-Level Audit Logging
    • Manage
      • Back up and restore
        • Back up data
        • Restore data
        • Snapshot and restore data
      • Migrate data
        • Bulk import
        • Bulk export
      • Change cluster configuration
      • Diagnostics reporting
      • Upgrade a deployment
      • Grow cluster
    • Troubleshoot
      • Troubleshooting
      • Common error messages
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
        • Recover tserver/master
        • Replace a failed YB-TServer
        • Replace a failed YB-Master
      • Node level issues
        • Check servers
        • Inspect logs
        • System statistics
        • Disk failure
    • Contribute
      • Core database
        • Contribution checklist
        • Build the source
        • Configure a CLion project
        • Run the tests
  • YUGABYTE PLATFORM
    • Yugabyte Platform
      • Overview
        • Install
        • Configure
      • Install Yugabyte Platform
        • Prerequisites
        • Prepare the environment
        • Install software
        • Prepare nodes (on-prem)
        • Uninstall software
      • Configure Yugabyte Platform
        • Create admin user
        • Configure the cloud provider
        • Configure the backup target
        • Configure alerts and health checking
        • Create and edit instance tags
      • Create deployments
        • Multi-zone universe
        • Multi-region universe
        • Read replica cluster
      • Manage deployments
        • Start and stop processes
        • Add a node
        • Remove a node
        • Edit a universe
        • Edit configuration flags
        • Upgrade the YugabyteDB software
        • Delete a universe
        • Migrate to Helm 3
      • Back up and restore universes
        • Configure backup storage
        • Back up universe data
        • Restore universe data
        • Schedule data backups
      • Security
        • Security checklist
        • Customize ports
        • Authorization platform
        • Create a KMS configuration
        • Enable encryption at rest
        • Enable encryption in transit (TLS)
        • Network security
      • Alerts and monitoring
        • Live Queries dashboard
      • Troubleshoot
        • Install and upgrade issues
        • Universe issues
      • Administer Yugabyte Platform
        • Back up and restore Yugabyte Platform
  • YUGABYTE CLOUD
    • Yugabyte Cloud
      • Free tier
      • Create clusters
      • Monitor clusters
      • Create databases
      • Manage database access
      • Connect to clusters
  • REFERENCE
    • Reference
    • Architecture
      • Design goals
      • Key concepts
        • Universe
        • YB-TServer Service
        • YB-Master Service
      • Core functions
        • Universe creation
        • Table creation
        • Write IO path
        • Read IO path
        • High availability
      • Layered architecture
      • Query layer
        • Overview
      • DocDB transactions layer
        • Transactions overview
        • Transaction isolation levels
        • Explicit locking
        • Single-row transactions
        • Distributed transactions
        • Transactional IO path
      • DocDB sharding layer
        • Hash & range sharding
        • Tablet splitting
        • Colocated tables
      • DocDB replication layer
        • Replication
        • xCluster replication
        • Read replicas
        • Change data capture (CDC)
      • DocDB storage layer
        • Persistence
        • Performance
    • APIs
      • YSQL
        • The SQL language
          • SQL statements
            • ABORT
            • ALTER DATABASE
            • ALTER DEFAULT PRIVILEGES
            • ALTER DOMAIN
            • ALTER GROUP
            • ALTER POLICY
            • ALTER ROLE
            • ALTER SEQUENCE
            • ALTER TABLE
            • ALTER USER
            • BEGIN
            • CALL
            • COMMENT
            • COMMIT
            • COPY
            • CREATE AGGREGATE
            • CREATE CAST
            • CREATE DATABASE
            • CREATE DOMAIN
            • CREATE EXTENSION
            • CREATE FUNCTION
            • CREATE GROUP
            • CREATE INDEX
            • CREATE OPERATOR
            • CREATE OPERATOR CLASS
            • CREATE POLICY
            • CREATE PROCEDURE
            • CREATE ROLE
            • CREATE RULE
            • CREATE SCHEMA
            • CREATE SEQUENCE
            • CREATE TABLE
            • CREATE TABLE AS
            • CREATE TRIGGER
            • CREATE TYPE
            • CREATE USER
            • CREATE VIEW
            • DEALLOCATE
            • DELETE
            • DO
            • DROP AGGREGATE
            • DROP CAST
            • DROP DATABASE
            • DROP DOMAIN
            • DROP EXTENSION
            • DROP FUNCTION
            • DROP GROUP
            • DROP OPERATOR
            • DROP OPERATOR CLASS
            • DROP OWNED
            • DROP POLICY
            • DROP PROCEDURE
            • DROP ROLE
            • DROP RULE
            • DROP SEQUENCE
            • DROP TABLE
            • DROP TRIGGER
            • DROP TYPE
            • DROP USER
            • END
            • EXECUTE
            • EXPLAIN
            • GRANT
            • INSERT
            • LOCK
            • PREPARE
            • REASSIGN OWNED
            • RESET
            • REVOKE
            • ROLLBACK
            • SELECT
            • SET
            • SET CONSTRAINTS
            • SET ROLE
            • SET SESSION AUTHORIZATION
            • SET TRANSACTION
            • SHOW
            • SHOW TRANSACTION
            • TRUNCATE
            • UPDATE
            • VALUES
          • WITH clause
            • WITH clause—SQL syntax and semantics
            • recursive CTE
            • case study—traversing an employee hierarchy
            • traversing general graphs
              • graph representation
              • common code
              • undirected cyclic graph
              • directed cyclic graph
              • directed acyclic graph
              • rooted tree
              • Unique containing paths
              • Stress testing find_paths()
            • case study—Bacon Numbers from IMDb
              • Bacon numbers for synthetic data
              • Bacon numbers for IMDb data
        • Data types
          • Array
            • array[] constructor
            • Literals
              • Text typecasting and literals
              • Array of primitive values
              • Row
              • Array of rows
            • FOREACH loop (PL/pgSQL)
            • array of DOMAINs
            • Functions and operators
              • ANY and ALL
              • Array comparison
              • Array slice operator
              • Array concatenation
              • Array properties
              • array_agg(), unnest(), generate_subscripts()
              • array_fill()
              • array_position(), array_positions()
              • array_remove()
              • array_replace() / set value
              • array_to_string()
              • string_to_array()
          • Binary
          • Boolean
          • Character
          • Date and time
          • JSON
            • JSON literals
            • Primitive and compound data types
            • Code example conventions
            • Indexes and check constraints
            • Functions & operators
              • ::jsonb, ::json, ::text (typecast)
              • ->, ->>, #>, #>> (JSON subvalues)
              • - and #- (remove)
              • || (concatenation)
              • = (equality)
              • @> and <@ (containment)
              • ? and ?| and ?& (key or value existence)
              • array_to_json()
              • jsonb_agg()
              • jsonb_array_elements()
              • jsonb_array_elements_text()
              • jsonb_array_length()
              • jsonb_build_object()
              • jsonb_build_array()
              • jsonb_each()
              • jsonb_each_text()
              • jsonb_extract_path()
              • jsonb_extract_path_text() and json_extract_path_text()
              • jsonb_object()
              • jsonb_object_agg()
              • jsonb_object_keys()
              • jsonb_populate_record()
              • jsonb_populate_recordset()
              • jsonb_pretty()
              • jsonb_set() and jsonb_insert()
              • jsonb_strip_nulls()
              • jsonb_to_record()
              • jsonb_to_recordset()
              • jsonb_typeof()
              • row_to_json()
              • to_jsonb()
          • Money
          • Numeric
          • Serial
          • UUID
        • Functions and operators
          • Aggregate functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • grouping sets, rollup, cube
            • Per function signature and purpose
              • avg(), count(), max(), min(), sum()
              • array_agg(), string_agg(), jsonb_agg(), jsonb_object_agg()
              • bit_and(), bit_or(), bool_and(), bool_or()
              • variance(), var_pop(), var_samp(), stddev(), stddev_pop(), stddev_samp()
              • linear regression
                • covar_pop(), covar_samp(), corr()
                • regr_%()
              • mode(), percentile_disc(), percentile_cont()
              • rank(), dense_rank(), percent_rank(), cume_dist()
            • case study—percentile_cont() and the "68–95–99.7" rule
            • case study—linear regression on COVID data
              • Download the COVIDcast data
              • Ingest the COVIDcast data
                • Inspect the COVIDcast data
                • Copy the .csv files to staging tables
                • Check staged data conforms to the rules
                • Join the staged data into a single table
                • SQL scripts
                  • Create cr_staging_tables()
                  • Create cr_copy_from_scripts()
                  • Create assert_assumptions_ok()
                  • Create xform_to_covidcast_fb_survey_results()
                  • ingest-the-data.sql
              • Analyze the COVIDcast data
                • symptoms vs mask-wearing by day
                • Data for scatter-plot for 21-Oct-2020
                • Scatter-plot for 21-Oct-2020
                • SQL scripts
                  • analysis-queries.sql
                  • synthetic-data.sql
          • currval()
          • lastval()
          • nextval()
          • Window functions
            • Informal functionality overview
            • Invocation syntax and semantics
            • Per function signature and purpose
              • row_number(), rank() and dense_rank()
              • percent_rank(), cume_dist() and ntile()
              • first_value(), nth_value(), last_value()
              • lag(), lead()
              • Tables for the code examples
                • table t1
                • table t2
                • table t3
                • table t4
            • case study—analyzing a normal distribution
              • Bucket allocation scheme
              • do_clean_start.sql
              • cr_show_t4.sql
              • cr_dp_views.sql
              • cr_int_views.sql
              • cr_pr_cd_equality_report.sql
              • cr_bucket_using_width_bucket.sql
              • cr_bucket_dedicated_code.sql
              • do_assert_bucket_ok
              • cr_histogram.sql
              • cr_do_ntile.sql
              • cr_do_percent_rank.sql
              • cr_do_cume_dist.sql
              • do_populate_results.sql
              • do_report_results.sql
              • do_compare_dp_results.sql
              • do_demo.sql
              • Reports
                • Histogram report
                • dp-results
                • compare-dp-results
                • int-results
        • Extensions
        • Keywords
        • Reserved names
      • YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • EXPLAIN
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple expressions
        • Subscripted expressions
        • Function call
        • Operators
        • BLOB
        • BOOLEAN
        • Collection
        • FROZEN
        • INET
        • Integer and counter
        • Non-integer
        • TEXT
        • DATE, TIME, and TIMESTAMP
        • UUID and TIMEUUID
        • JSONB
        • Date and time
        • BATCH
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • ysqlsh
      • ycqlsh
      • yb-admin
      • yb-ts-cli
      • ysql_dump
      • ysql_dumpall
    • Configuration
      • yb-tserver
      • yb-master
      • yugabyted
      • Default ports
    • Drivers
      • Client drivers for YSQL API
      • YugabyteDB JDBC Driver
      • Client drivers for YCQL
      • Spring Data YugabyteDB
    • Connectors
      • Kafka Connect YugabyteDB
    • Third party tools
      • DBeaver
      • DbSchema
      • pgAdmin
      • SQL Workbench/J
      • TablePlus
      • Visual Studio Code
    • Sample datasets
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
  • RELEASES
    • Releases
    • Releases overview
    • Release versioning
    • What's new
      • v2.5 (latest)
      • v2.4.0 (stable)
    • Earlier releases
      • v2.3.3
      • v2.3.2
      • v2.3.1
      • v2.3.0
      • v2.1.8
      • v2.1.6
      • v2.2.0
      • v2.1.5
      • v2.1.4
      • v2.1.3
      • v2.1.2
      • v2.1.1
      • v2.1.0
      • v2.0.11
      • v2.0.10
      • v2.0.9
      • v2.0.8
      • v2.0.7
      • v2.0.6
      • v2.0.5
      • v2.0.3
      • v2.0.1
      • v2.0.0
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • FAQ
    • Comparisons
      • Amazon Aurora
      • Google Cloud Spanner
      • CockroachDB
      • TiDB
      • Vitess
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • PostgreSQL
      • Redis in-memory store
      • Apache HBase
    • FAQs
      • General FAQ
      • Operations FAQ
      • API compatibility FAQ
      • Yugabyte Platform FAQ
  • MISC
    • YEDIS
      • Quick start
      • Develop
        • Build an application
        • C#
        • C++
        • Go
        • Java
        • NodeJS
        • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
    • Legal
      • Third party software
> APIs > YSQL > Data types > Array > Functions and operators >

Operators for comparing two arrays

  • Comparison operators overview
    • Comparison criteria
    • Pairwise comparison of values
    • The priority of differences among the geometric properties
  • Containment and overlap operators overview
  • Examples for each operator
    • The   =   and   <>   operators
    • The   >   and   >=   and   <=   and   <   and   <>   operators
    • The   @>   and   <@   operators
    • The   &&   operator
  • Equality and inequality semantics
  • Containment and overlap operators semantics

Comparison operators overview

Purpose: Each of the comparison operators returns TRUE or FALSE according to the outcome of the particular comparison test between the input LHS and RHS arrays.

Signature

These operators all have the same signature, thus:

input value:       anyarray, anyarray
return value:      boolean

Note: These operators require that the LHS and RHS arrays have the same data type. (It's the same rule for the comparison of scalars.) However, they do not require that the arrays have identical geometric properties. Rules are defined so that a difference between one or more of these properties does not mean that comparison is disallowed. Rather, the LHS array might be deemed to be less than, or greater than, the RHS array. It's essential, therefore, to understand the comparison algorithm.

Comparison criteria

These are the unique characteristics of an array with respect to the algorithm that compares two array values:

  • the actual values, compared pairwise in row-major order
  • the cardinality
  • the number of dimensions
  • the lower bound on each dimension.

The term "row-major order" is explained in Joint semantics within the section "Functions for reporting the geometric properties of an array".

The other geometric properties (the length and upper bound along each dimension) can be derived from the properties that the bullets list..

There is, of course, a well-defined priority among the comparisons. Briefly, value comparison is done first. Then, but only if no difference is detected, are the geometric properties compared.

Pairwise comparison of values

The first comparison test scans the values in each of the LHS and RHS arrays in row-major order (see Joint semantics) and does a pairwise comparison. Notably, the comparison rule non-negotiably uses IS NOT DISTINCT FROM semantics. Moreover, when a not null array value is pairwise compared with a NULL value, the not null value is deemed to be less than the NULL value.

Notice the contrast with the = operator comparison rule for free-standing scalar values. This comparison uses NULL semantics but, of course, lets you use IS NOT DISTINCT FROM comparison if this better suits your purpose.

Otherwise, the comparison rules are the same as those for scalar values and, by extension, with those for, for example, "row" type values.

If a pairwise comparison results in inequality, then the LHS and RHS arrays are immediately deemed to be unequal with no regard to the geometric properties. The outcome of the first pairwise comparison, when these values differ, determines the outcome of the array comparison. Remaining pairwise comparisons are not considered.

Notice that the two arrays might not have the same cardinality. If all possible pairwise comparisons result in equality, then the array with the greater cardinality is deemed to be greater than the other array, and the other geometric properties are not considered.

The priority of differences among the geometric properties

The previous section stated the rule that the cardinality comparison has the highest priority among the geometric property comparisons. And that this rule kicks in only of all possible value comparisons result in equality.

When both all possible value comparisons and the cardinality comparison result in equality, then the comparison between the number of dimensions has a higher priority than the comparison between the lower bound on each dimension. Of course, the array with the greater number of dimensions is deemed to be the greater array.

This means that the lower bounds are significant when two arrays are compared only when they are identical in pairwise value comparison, cardinality, and the number of dimensions. Then the array with the greater lower bound, in dimension order, is deemed to be the greater array.

Equality and inequality semantics demonstrates each of the rules that this "Comparison operators overview" section has stated.

Containment and overlap operators overview

These three operators are insensitive to the geometric properties of the two to-be-compared arrays.

  • The two containment operators test if the distinct set of values in one array contains, or is contained by, the distinct set of values in the other array.
  • The overlap operator tests if the distinct set of values in one array and the distinct set of values in the other array have at least one value in common.

Containment and overlap operators semantics below demonstrates each of the rules that this section has stated.

Examples for each operator

The   =   and   <>   operators

  • The = operator returns TRUE if the LHS and RHS arrays are equal.
  • The <> operator is the natural complement: it returns TRUE if the LHS and RHS arrays are not equal.
with
  v as (
    select
      (select array['a', 'b', null, 'd']::text[]) as a1,
      (select      '{a,   b,  null,  d}'::text[]) as a2
  )
select (a1 = a2)::text as "EQUALITY comparison result"
from v;

This is the result:

 EQUALITY comparison result 
----------------------------
 true
with
  v as (
    select
      (select array['a', 'b', 'c',  'd']::text[]) as a1,
      (select      '{a,   b,  null,  d}'::text[]) as a2
  )
select (a1 <> a2)::text as "INEQUALITY comparison result"
from v;

This is the result:

 INEQUALITY comparison result
------------------------------
 true

The   >   and   >=   and   <=   and   <   and   <>   operators

These four operators implement the familiar inequality comparisons.

  • The > operator returns TRUE if the LHS array is greater than the RHS array.
  • The >= operator returns TRUE if the LHS array is greater than or equal to the RHS array.
  • The <= operator returns TRUE if the LHS array is less than or equal to the RHS array.
  • The < operator returns TRUE if the LHS array is less than the RHS array.

It's sufficient, therefore, to provide an example for just the < operator.

with
  v as (
    select
      (select array['a', 'b', 'c',  'd']::text[]) as a1,
      (select array['a', 'b', 'e',  'd']::text[]) as a2,
      (select      '{a,   b,  null,  d}'::text[]) as a3
  )
select
  (a1 < a2)::text as "'LESS THAN' comparison result 1",
  (a1 < a3)::text as "'LESS THAN' comparison result 2"
from v;

This is the result:

 'LESS THAN' comparison result 1 | 'LESS THAN' comparison result 2 
---------------------------------+---------------------------------
 true                            | true

The   @>   and   <@   operators

  • The @> operator returns TRUE if the LHS array contains the RHS array—that is, if every distinct value in the RHS array is found among the LHS array's distinct values.
  • The <@ operator is the natural complement: it returns TRUE if every distinct value in the LHS array is found among the RHS array's distinct values.
with
  v as (
    select
      (select array['a', 'b', 'c',  'd']::text[]) as a1,
      (select array['a',      'c'      ]::text[]) as a2
  )
select
  (a1 @> a2)::text as "CONTAINS comparison result",
  (a2 <@ a1)::text as "'IS CONTAINED BY' comparison result"
from v;

This is the result:

 CONTAINS comparison result | 'IS CONTAINED BY' comparison result 
----------------------------+-------------------------------------
 true                       | true

The   &&   operator

The && operator returns TRUE if the LHS and RHS arrays overlap—that is, if they have at least one value in common. The definition of this operator makes it insensitive to which of the two to-be-compared is used on the LHS and which is used on the RHS.

with
  v as (
    select
      (select array['a', 'b', 'c',  'd']::text[]) as a1,
      (select array['d', 'e', 'f',  'g']::text[]) as a2
  )
select
  (a1 && a2)::text as "'a1 OVERLAPS a2' comparison result",
  (a2 && a1)::text as "'a2 OVERLAPS a1' comparison result"
from v;

This is the result:

 'a1 OVERLAPS a2' comparison result | 'a2 OVERLAPS a1' comparison result 
------------------------------------+------------------------------------
 true                               | true

Equality and inequality semantics

This section demonstrates each of the rules that Comparison operators overview above stated.

-- Any two arrays can be compared without error if they have the same data type.
do $body$
begin
  ------------------------------------------------------------------------------
  -- Illustrate "IS NOT DISTINCT FROM" semantics.
  declare
    v1 constant int := 1;
    v2 constant int := 1;
    n1 constant int := null;
    n2 constant int := null;
  begin
    assert
      (v1 = v2)                    and
      (v1 is not distinct from v2) and

      ((n1 = n2) is null)          and
      (n1 is not distinct from n2),
    'unexpected';
  end;

  ------------------------------------------------------------------------------
  -- Basic demonstration of equaliy when the geom. properties of
  -- the two arrays are identical.
  -- Shows that pairwise comparison uses "IS NOT DISTINCT FROM" semantics and NOT
  -- the conventional NULL semantics used when scalars are compared.
  declare
    a constant int[] := '{10, null, 30}';
    b constant int[] := '{10, null, 30}'; -- Identical to a.
  begin
    assert
      (a = b),
    '"a = b" assert failed';

    -- Because of this, there's no need ever to write this.
    assert
      (a is not distinct from b),
    '"a is not distinct from b" assert failed';
  end;

  ------------------------------------------------------------------------------
  -- Basic demonstration of inequality when the geometric properties of
  -- the two arrays are identical.
  -- When the first difference is encountered in row-major order, the comparison
  -- is made. Other differences are irrelevant.
  declare
    a constant int[] := '{10, 20, 30}';
    b constant int[] := '{10, 19, 31}'; 
  begin
    assert
      (a <> b) and
      (a >  b) and
      (a >= b) and
      (b <= a) and
      (b <  a) ,
    '"a > b" assert failed';
  end;

  ------------------------------------------------------------------------------
  -- Demonstration of inequality when the geometric properties of
  -- the two arrays are identical.
  -- Here, the first pairwise difference is NOT NULL versus NULL.
  declare
    a constant int[] := '{10, 20,   30}';
    b constant int[] := '{10, null, 29}';
  begin
    -- Bizarrely, a NOT NULL value counts as LESS THAN a NULL value in the
    -- pairwise comparison.
    assert
      (a <> b) and
      (a <  b),
    '"a < b" assert failed';

    -- Again, because of this, there's no need ever to write this.
    assert
      (a is distinct from b) ,
    '"a is distinct from b" assert failed';
  end;

  ------------------------------------------------------------------------------
  -- Extreme demonstration of priority.
  -- c has just a single value and d has several.
  -- c has one dimension and d has two.
  -- c's first lower bound is less than d's first lower.
  -- d's second lower bound is greater than one, but is presumably irrelevant.
  -- But c's first value is GREATER THAN d's first value,
  -- scanning in row-major order.
  --
  -- Pairwise value comparison has the hoghest priority.
  -- therefore c is deemed to be GREATER THAN d.
  
  declare
    c constant int[] := '{2}';

    -- Notice that d's first value is at [2][3].
    d constant int[] := '[2:3][3:4]={{1, 2}, {3, 3}}';

  begin
    assert
      cardinality(c) < cardinality(d),
    '"cardinality(c) < cardinality(d)" assert failed';

    assert
      array_ndims(c) < array_ndims(d),
    '"ndims(c) < ndims(d)" assert failed';
    assert
      array_lower(c, 1) < array_lower(d, 1),
    '"lower(c, 1) < lower(d, 1)" assert failed';

    assert
      c[1] > d[2][3],
    '"c[1] > d[2][3]" assert failed';

   assert
     c > d,
   '"c > d" assert failed';
  end;

  ------------------------------------------------------------------------------
  -- Pairwise comparison is equal are far as it is feasible.
  -- e's ndims < f's.
  -- e's lb-1 < f's.
  -- BUT e's cardinality > f's.
  -- Cardinality has highest priority among the geom. propoerties,
  -- so e is deemed to be GREATER THAN f.
  declare
    e constant int[] := '{10, 20, 30, 40, 50, 60, 70}';
    f constant int[] := '[2:3][3:5]={{10, 20, 30}, {40, 50, 60}}'; 
  begin
    assert
      e[1] = f[2][3] and
      e[2] = f[2][4] and
      e[3] = f[2][5] and
      e[4] = f[3][3] and
      e[5] = f[3][4] and
      e[6] = f[3][5] ,
    '"e-to-f" eqality test, as far as feasible, assert failed';

    assert
      array_ndims(e) < array_ndims(f),
    '"ndims(e) < ndims(f)" assert failed';

    assert
      array_lower(e, 1) < array_lower(f, 1),
    '"lower(e, 1) < lower(f, 1)" assert failed';

    assert
      cardinality(e) > cardinality(f),
    '"cardinality(e) > cardinality(f)" assert failed';

    assert
      (e > f) ,
    'e > f assert failed';
  end;

  ------------------------------------------------------------------------------
  -- g's cardinality = h's.
  -- So pairwise comparison is feasible for all values, and is equal.
  -- g's ndims > h's.
  -- g's lb-1 < h's.
  -- Ndims has higher priority among ndims and lower bounds,
  -- so g is deemed to be GREATER THAN h.
  declare
    g constant int[] := '{{10, 20, 30}, {40, 50, 60}}'; 
    h constant int[] := '[2:7]={10, 20, 30, 40, 50, 60}';
  begin
    assert
      cardinality(g) = cardinality(h),
    '"cardinality(g) = cardinality(h)" assert failed';

    assert
      g[1][1] = h[2] and
      g[1][2] = h[3] and
      g[1][3] = h[4] and
      g[2][1] = h[5] and
      g[2][2] = h[6] and
      g[2][3] = h[7] ,
    '"g-to-h" eqality test assert failed';

    assert
      array_ndims(g) > array_ndims(h),
    '"ndims(g) > ndims(h)" assert failed';

    assert
      array_lower(g, 1) < array_lower(h, 1),
    '"lower(g, 1) < lower(h, 1)" assert failed';

    assert
      (g > h) ,
    '"g > h" assert failed';
  end;

  ------------------------------------------------------------------------------
  declare
    i constant int[] := '[5:6][4:6]={{10, 20, 30}, {40, 50, 60}}'; 
    j constant int[] := '[3:4][6:8]={{10, 20, 30}, {40, 50, 60}}';
  begin
    assert
      cardinality(i) = cardinality(j),
    '"cardinality(i) = cardinality(j)" assert failed';

    assert
      i[5][4] = j[3][6] and
      i[5][5] = j[3][7] and
      i[5][6] = j[3][8] and
      i[6][4] = j[4][6] and
      i[6][5] = j[4][7] and
      i[6][6] = j[4][8] ,
    '"i-to-j" eqality test assert failed';

    assert
      array_ndims(i) = array_ndims(j),
    '"ndims(i) = ndims(j)" assert failed';

    assert
      array_lower(i, 1) > array_lower(j, 1),
    '"lower(i, 1) > lower(j, 1)" assert failed';

    assert
      (i > j) ,
    '"i > j" assert failed';
  end;

  ------------------------------------------------------------------------------
end;
$body$;

Containment and overlap operators semantics

This section demonstrates each of the rules that Containment and overlap operators overview stated.

-- Any two arrays can be compared without error if they have the same data type.
-- Insensitive to the geometric properties.
do $body$
declare
  a constant int[] := '[2:3][4:5]={{10, 20}, {30, 40}}';
  b constant int[] := '[5:6]={20, 30}';
  c constant int[] := '[6:9]={40, 50, 70, 70}';
  d constant int[] := '[2:4]={50, 60, 70}';
begin
  assert
    -- Containment
    (b @> b) and
    (b <@ a) and

    -- Overlap.
    -- The definition of the semantics makes the LHS, RHS order immaterial.
    (a && c) and
    (c && a) and

    -- a and d have NO values in common.
    not (a && d),
  'unexpected';
end;
$body$;
  • Comparison operators overview
    • Comparison criteria
    • Pairwise comparison of values
    • The priority of differences among the geometric properties
  • Containment and overlap operators overview
  • Examples for each operator
    • The   =   and   <>   operators
    • The   >   and   >=   and   <=   and   <   and   <>   operators
    • The   @>   and   <@   operators
    • The   &&   operator
  • Equality and inequality semantics
  • Containment and overlap operators semantics
Ask our community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2020 Yugabyte, Inc. All rights reserved.