Use smlar to calculate array similarity on PostgreSQL - ApsaraDB RDS

The smlar extension calculates the similarity between two arrays of the same data type. All built-in PostgreSQL data types are supported.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance running PostgreSQL 10 or later
(Conditional) If smlar is unavailable on a qualifying instance, the minor engine version updated to the required version. For PostgreSQL 17, the minor engine version must be 20241030 or later. For more information, see Update the minor engine version.

Function signature	Description
`float4 smlar(anyarray, anyarray)`	Calculates the similarity between two arrays of the same data type.
`float4 smlar(anyarray, anyarray, bool useIntersect)`	Calculates the similarity between two arrays of composite data types.
`float4 smlar(anyarray a, anyarray b, text formula)`	Calculates the similarity using a custom formula.
`anyarray % anyarray`	Returns `true` if similarity > `smlar.threshold`; otherwise `false`.
`text[] tsvector2textarray(tsvector)`	Converts the `tsvector` type to the `text` type.
`anyarray array_unique(anyarray)`	Sorts elements, excluding duplicates, in an array.
`float4 inarray(anyarray, anyelement)`	Returns `1` if element exists in array; otherwise `0`.
`float4 inarray(anyarray, anyelement, float4, float4)`	Returns the third parameter if element exists; otherwise the fourth parameter.
`float4 set_smlar_limit(float4)`	Sets the `smlar.threshold` parameter.
`float4 show_smlar_limit()`	Displays the `smlar.threshold` parameter value.

Returns the similarity score between two arrays of the same data type as a float4 value.

Calculates similarity between two arrays of composite data types. The composite type must follow this definition:

CREATE TYPE type_name AS (element_name anytype, weight_name FLOAT4);

The useIntersect parameter controls which elements are included in the calculation:

Calculates similarity using a formula you specify. The formula can reference these predefined variables:

Variable	Description
`N.i`	Number of common elements in both arrays
`N.a`	Number of distinct elements in array `a`
`N.b`	Number of distinct elements in array `b`

For the full list of supported data types and parameters, see the smlar repository on GitHub.

Connect to your database, then create the extension:
```
testdb=> create extension smlar;
```

Calculate the similarity between two arrays:

testdb=> SELECT smlar('{1,4,6}'::int[], '{5,4,6}' );
  smlar
----------
 0.666667
(1 row)

Calculate similarity using a custom formula. The formula is evaluated against the predefined variables N.i, N.a, and N.b:
```
testdb=> SELECT smlar('{1,4,6}'::int[], '{5,4,6}', 'N.i / sqrt(N.a * N.b)' );
  smlar
----------
 0.666667
(1 row)
```
Remove the extension when no longer needed:
```
testdb=> drop extension smlar;
```