如何创建一个约束条件，使数组中所有值的唯一性得到保证：
CREATE TABLE mytable
(
interface integer[2],
CONSTRAINT link_check UNIQUE (sort(interface))
)
我的排序函数
create or replace function sort(anyarray)
returns anyarray as $$
select array(select $1[i] from generate_series(array_lower($1,1),
array_upper($1,1)) g(i) order by 1)
$$ language sql strict immutable;
我需要让值{10，22}和{22，10}被视为相同，并在唯一约束条件下进行检查
我认为你不能在唯一约束中使用函数，但是可以在唯一的索引中使用。所以，假设有一个类似于以下代码的排序函数：
create function sort_array(anyarray) returns anyarray as $$
select array_agg(distinct n order by n) from unnest($1) as t(n);
$$ language sql immutable;
create table mytable (
interface integer[2]
);
create unique index mytable_uniq on mytable (sort_array(interface));
接着发生以下事件：
=> insert into mytable (interface) values (array[11,23]);
INSERT 0 1
=> insert into mytable (interface) values (array[11,23]);
ERROR: duplicate key value violates unique constraint "mytable_uniq"
DETAIL: Key (sort_array(interface))=({11,23}) already exists.
=> insert into mytable (interface) values (array[23,11]);
ERROR: duplicate key value violates unique constraint "mytable_uniq"
DETAIL: Key (sort_array(interface))=({11,23}) already exists.
=> insert into mytable (interface) values (array[42,11]);
INSERT 0 1
我注意到使用的函数。对于只有两个整数数组来说，它们似乎过于复杂了。也许这只是对实际情况的简化？无论如何，我很感兴趣，并进行了几个变量的测试。
临时表格包含10000个随机整数对：
CREATE TEMP TABLE arr (i int[]);
INSERT INTO arr
SELECT ARRAY[(random() * 1000)::int, (random() * 1000)::int]
FROM generate_series(1,10000);
测试候选人，附带简短的注释来解释每一个：
CREATE OR REPLACE FUNCTION sort_array1(integer[])
RETURNS int[]
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT array_agg(n) FROM (SELECT n FROM unnest($1) AS t(n) ORDER BY n) AS a;
$func$;
ORDER BY（pg 9.0+）与之前相同。CREATE OR REPLACE FUNCTION sort_array2(int[])
RETURNS int[]
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT array_agg(n ORDER BY n) FROM unnest($1) AS t(n);
$func$;
CREATE OR REPLACE FUNCTION sort_array3(anyarray)
RETURNS anyarray
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT ARRAY(
SELECT $1[i]
FROM generate_series(array_lower($1,1), array_upper($1,1)) g(i)
ORDER BY 1)
$func$;
int[]CREATE OR REPLACE FUNCTION sort_array4(int[])
RETURNS int[]
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT ARRAY(
SELECT $1[i]
FROM generate_series(array_lower($1,1), array_upper($1,1)) g(i)
ORDER BY 1)
$func$;
array_lower()函数 - 它总是返回1。CREATE OR REPLACE FUNCTION sort_array5(int[])
RETURNS int[]
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT ARRAY(
SELECT $1[i]
FROM generate_series(1, array_upper($1,1)) g(i)
ORDER BY 1)
$func$;
CREATE OR REPLACE FUNCTION sort_array6(int[])
RETURNS int[]
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT ARRAY(
SELECT i
FROM (VALUES ($1[1]),($1[2])) g(i)
ORDER BY 1)
$func$;
CREATE OR REPLACE FUNCTION sort_array7(int[])
RETURNS int[]
LANGUAGE sql STRICT IMMUTABLE AS
$func$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$func$;
STRICT 修饰符！CREATE OR REPLACE FUNCTION sort_array8(int[])
RETURNS int[]
LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$func$;
我执行了每个约20次，并从EXPLAIN ANALYZE中选择了最佳结果。
SELECT sort_array1(i) FROM arr -- Total runtime: 183 ms
SELECT sort_array2(i) FROM arr -- Total runtime: 175 ms
SELECT sort_array3(i) FROM arr -- Total runtime: 183 ms
SELECT sort_array4(i) FROM arr -- Total runtime: 183 ms
SELECT sort_array5(i) FROM arr -- Total runtime: 177 ms
SELECT sort_array6(i) FROM arr -- Total runtime: 144 ms
SELECT sort_array7(i) FROM arr -- Total runtime: 103 ms
SELECT sort_array8(i) FROM arr -- Total runtime: 43 ms (!!!)
这些是在Debian Squeeze上v9.0.5服务器的结果。在v8.4上也有类似的结果。
我还测试了一些PL/pgSQL变体，它们比预期的要慢一些：对于微小操作来说开销太大，没有查询计划可以缓存。
简单函数（第7个）比其他函数快得多。这是预料之中的，其他变体的开销对于微小数组来说太大了。
去掉STRICT声明会使速度增加一倍以上。我没有预料到这一点，并发布了这篇后续文章以揭示原因：
create unique index ix on
mytable(least(interface[1], interface[2]), greatest(interface[1], interface[2]));