Set equality

From PostgreSQL wiki
Jump to navigationJump to search

Snippets

Set equality

Works with PostgreSQL

all maintained versions

Written in

SQL

Depends on

Nothing


Array equality is sensitive to the order and duplication within the arrays, so it is not suitable for testing arrays as if they were sets.

Set equality can obtained by testing arrays for "contains and is contained by".

Implementation

This is a convenience operator. It is not performance optimized, does not support indexes, and does not consider multidimensional arrays.

create function contains_contained_by (anyarray, anyarray) returns boolean 
language SQL as $$
    select $1 <@ $2 and $1 @> $2 
$$
parallel safe --omit this line prior to version 9.6 
immutable; 

create operator <@> (
    procedure = contains_contained_by, 
    leftarg = anyarray, 
    rightarg = anyarray
);

Usage

 select '{c,a,b}'::text[] <@> '{b,a,c,b}'::text[];