Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

contrib package for working with 1-D arrays

License

NotificationsYou must be signed in to change notification settings

postgrespro/anyarray

 
 

Repository files navigation

Introduction

AnyArray – is a PostgreSQL extension which implements 1-D anyarrayfunctionality.

Authors

Availability

AnyArray is released as an extension and not available in default PostgreSQLinstallation. It is available fromgithubunder the same license asPostgreSQLand supports PostgreSQL 9.1+.

Installation

Before build and install AnyArray you should ensure following:

  • PostgreSQL version is 9.1 or higher.
  • You have development package of PostgreSQL installed or you builtPostgreSQL from source.
  • Your PATH variable is configured so that pg_config command available.

Typical installation procedure may look like this:

$ git clone https://github.com/postgrespro/anyarray.git$ cd anyarray$ make USE_PGXS=1$ sudo make USE_PGXS=1 install$ make USE_PGXS=1 installcheck$ psql DB -c "CREATE EXTENSION anyarray;"

Usage

Anyarray functions

FunctionDescriptionExamples
anyset(int) →int[1]anyset(1234) →ARRAY[1234]
icount(anyarray)→intReturns the length of anyarray. icount() returns 0 for empty arrays.icount( '{1234234, 0234234}'::int[] ) →2
sort(anyarray [, 'asc'|'desc'])→anyarrayReturns the anyarray sorted in an ascending (default) or descending order.sort( '{1234234, -30, 0234234}'::int[],'desc') →{1234234, 234234, -30}
sort_asc(anyarray)→anyarrayReturns the anyarray sorted in an ascending order.sort_asc( '{1234234,-30, 0234234}'::int[]) →{-30,234234,1234234}
sort_desc(anyarray)→anyarrayReturns the anyarray sorted in a descending order.sort( '{1234234, -30, 0234234}'::int[],'desc' ) →{1234234,234234,-30}
uniq(anyarray)→anyarrayReturns anyarray where consequent repeating elements replaced by one element. If you need to remove all repeating elements in array, you can sort array and apply uniq() function.uniq( '{1234234, -30, -30, 0234234, -30}'::int[]) →{1234234, -30, 234234, -30} ,uniq( sort_asc( '{1234234, -30, -30, 0234234, -30}'::int[] ) ) →{-30,234234,1234234}
uniq_d(anyarray)→anyarrayReturns only consequent repeating elements. If you need to return all repeating elements, you can sort array and apply uniq_d() functionuniq_d( '{1234234, -30, -30, 0234234, -30, 0234234}'::int[] ) →{-30},uniq_d( sort_asc('{1234234, -30,-30, 0234234, -30, 0234234}'::int[] ) ) →{-30,234234}
idx(anyarray, searchelement)→intReturns the position of the searchelement first occurance in the arrayidx( '{1234234,-30,-30,0234234,-30}'::int[], -30 ) →2
subarray(anyarray, start int, length int)→anyarrayReturns the subarray from original array. If the start position value is negative, it is counted from the end of the original array (-1 means last element, -2 means element before last etc.)subarray( '{1234234, -30, -30, 0234234, -30}'::int[],2,3 ) →{-30, -30, 234234} ,subarray( '{1234234, -30, -30, 0234234, -30}'::int[], -1, 1 ) →{-30},subarray( '{1234234, -30, -30, 0234234, -30}'::int[], 0, -1 ) →{1234234, -30, -30, 234234}

Anyarray operators

OperatorDescriptionExamples
#anyarray →intReturns the length of anyarray.#'{1234234,0234234}'::int[] →2
anyarray + anyarray→anyarrayReturns the union of arrays'{123,623,445}'::int[] + 1245 →{123,623,445,1245} ,'{123,623,445}'::int[] + '{1245,87,445}' →{123,623,445,1245,87,445}
anyarray - anyarray→anyarrayReturns the substraction of left array and right array'{123,623,445}'::int[] - 623 →{123,445},'{123,623,445}'::int[] - '{1623,623}'::int[]{123,445}
anyarray | anyarray→anyarrayReturns the union of array, repeating elements are excluded from resulting array.'{123,623,445}'::int[] |{1623,623}'::int[] →{123,445,623,1623}
anyarray & anyarray→anyarrayReturns arrays intersection.'{1,3,1}'::int[] & '{1,2}' →{1}

Anyarray operator class strategies

OperatorGIST and GIN Strategy numDescription
anyarray &&anyarrayRTOverlapStrategyNumber 3Overlapped
anyarray =anyarrayRTSameStrategyNumber 6Same
anyarray @>anyarrayRTContainsStrategyNumber 7Contains
anyarray <@anyarrayRTContainedByStrategyNumber 8Contained
anyarray %anyarrayAnyAarraySimilarityStrategy 16Similarity

Similarity search options

Set distance type for similarity search.

SET anyarray.similarity_type=cosine;SET anyarray.similarity_type=jaccard;SET anyarray.similarity_type=overlap;

Set threshold for similarity search.

SET anyarray.similarity_threshold = 3;RESET anyarray.similarity_threshold;

Examples

Examples for INTEGER[] .

Create a table with sample data.

SELECT t, ARRAY(SELECT v::int4FROM generate_series(max(0, t - 10),  t) as v) AS vINTO test_int4FROM generate_series(1, 200) as t;

Similarity calculation.

SET anyarray.similarity_type=cosine;SELECT  t, similarity(v, '{10,9,8,7,6,5,4,3,2,1}') AS s FROM test_int4 WHERE v % '{10,9,8,7,6,5,4,3,2,1}' ORDER BY s DESC, t;SELECT  t, similarity(v, '{50,49,8,7,6,5,4,3,2,1}') AS s FROM test_int4 WHERE v % '{50,49,8,7,6,5,4,3,2,1}' ORDER BY s DESC, t;SET anyarray.similarity_type=jaccard;SELECT  t, similarity(v, '{10,9,8,7,6,5,4,3,2,1}') AS s FROM test_int4 WHERE v % '{10,9,8,7,6,5,4,3,2,1}' ORDER BY s DESC, t;SELECT  t, similarity(v, '{50,49,8,7,6,5,4,3,2,1}') AS s FROM test_int4 WHERE v % '{50,49,8,7,6,5,4,3,2,1}' ORDER BY s DESC, t;SELECT t, v FROM test_int4 WHERE v && '{43,50}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v @> '{43,50}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v <@ '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v =  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=cosine;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=jaccard;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=overlap;SET anyarray.similarity_threshold = 3;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;RESET anyarray.similarity_threshold;

Create GIST index.

CREATE INDEX idx_test_int4 ON test_int4 USING gist (v _int4_aa_ops);SET enable_seqscan=off;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v && '{43,50}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v @> '{43,50}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v <@ '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v =  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v && '{43,50}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v @> '{43,50}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v <@ '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v =  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=cosine;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=jaccard;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=overlap;SET anyarray.similarity_threshold = 3;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;RESET anyarray.similarity_threshold;DROP INDEX idx_test_int4;

Create GIN index.

CREATE INDEX idx_test_int4 ON test_int4 USING gin (v _int4_aa_ops);SET enable_seqscan=off;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v && '{43,50}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v @> '{43,50}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v <@ '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v =  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;EXPLAIN (COSTS OFF) SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v && '{43,50}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v @> '{43,50}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v <@ '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SELECT t, v FROM test_int4 WHERE v =  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=cosine;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=jaccard;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;SET anyarray.similarity_type=overlap;SET anyarray.similarity_threshold = 3;SELECT t, v FROM test_int4 WHERE v %  '{0,1,2,3,4,5,6,7,8,9,10}' ORDER BY t;RESET anyarray.similarity_threshold;

Operator class names for all types implemented in anyarray.

TypeGISTGIN
bit_bit_aa_ops_bit_aa_ops
bytea_bytea_aa_ops_bytea_aa_ops
char_char_aa_ops_char_aa_ops
cidr_cidr_aa_ops_cidr_aa_ops
date_date_aa_ops_date_aa_ops
float4_float4_aa_ops_float4_aa_ops
float8_float8_aa_ops_float8_aa_ops
inet_inet_aa_ops_inet_aa_ops
int2_int2_aa_ops_int2_aa_ops
int4_int4_aa_ops_int4_aa_ops
int8_int8_aa_ops_int8_aa_ops
interval_interval_aa_ops_interval_aa_ops
macaddr_macaddr_aa_ops_macaddr_aa_ops
money_money_aa_ops_money_aa_ops
numeric_numeric_aa_ops_numeric_aa_ops
oid_oid_aa_ops_oid_aa_ops
text_text_aa_ops_text_aa_ops
time_time_aa_ops_time_aa_ops
timestamp_timestamp_aa_ops_timestamp_aa_ops
timestamptz_timestamptz_aa_ops_timestamptz_aa_ops
timetz_timetz_aa_ops_timetz_aa_ops
varbit_varbit_aa_ops_varbit_aa_ops
varchar_varchar_aa_ops_varchar_aa_ops

About

contrib package for working with 1-D arrays

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published
[8]ページ先頭
©2009-2025 Movatter.jp