pg中多值列_postgresql – 如何使用多列中的值分發表？

Citus不支援按多列分發.但是,您可以建立一個複合類型和

partition your data by that composite type.

– 如果連結失效,下面将連結内容連結 –

在複合類型上進行散列分區的步驟

>在主節點和所有工作節點上建立類型：

CREATE TYPE new_composite_type as (project_key text, date text);

>建立用于檢查相等性的函數,并将其與新類型的相等運算符相關聯

CREATE FUNCTION equal_test_composite_type_function(new_composite_type, new_composite_type) RETURNS boolean

AS 'select $1.project_key = $2.project_key AND $1.date = $2.date;'

LANGUAGE SQL

IMMUTABLE

RETURNS NULL ON NULL INPUT;

-- ... use that function to create a custom equality operator...

CREATE OPERATOR = (

LEFTARG = new_composite_type,

RIGHTARG = new_composite_type,

PROCEDURE = equal_test_composite_type_function,

HASHES

);

>建立一個新的哈希函數.

注意：這隻是一個簡單的例子,可能無法提供良好的均勻哈希分布.有幾個好的散列函數的例子可以在一個單獨的C函數而不是SQL中實作.

CREATE FUNCTION new_composite_type_hash(new_composite_type) RETURNS int

AS 'SELECT hashtext( ($1.project_key || $1.date)::text);'

LANGUAGE SQL

IMMUTABLE

RETURNS NULL ON NULL INPUT;

>為BTREE和HASH通路方法定義運算符類：

CREATE OPERATOR CLASS new_op_fam_btree_class

DEFAULT FOR TYPE new_composite_type USING BTREE AS

OPERATOR 3 = (new_composite_type, new_composite_type);

CREATE OPERATOR CLASS new_op_fam_hash_class

DEFAULT FOR TYPE new_composite_type USING HASH AS

OPERATOR 1 = (new_composite_type, new_composite_type),

FUNCTION 1 new_composite_type_hash(new_composite_type);

>使用新類型建立表并分發它.

CREATE TABLE composite_type_partitioned_table

(

id integer,

composite_column new_composite_type

);

SELECT master_create_distributed_table('composite_type_partitioned_table','composite_column', 'hash');

SELECT master_create_worker_shards('composite_type_partitioned_table', 4, 1);

>運作INSERT和SELECT.請注意,正确的修剪将需要引用,如這些查詢中所示.

INSERT INTO composite_type_partitioned_table VALUES (1, '("key1","20160101")'::new_composite_type);

INSERT INTO composite_type_partitioned_table VALUES (2, '("key1","20160102")'::new_composite_type);

INSERT INTO composite_type_partitioned_table VALUES (3, '("key2","20160101")'::new_composite_type);

INSERT INTO composite_type_partitioned_table VALUES (4, '("key2","20160102")'::new_composite_type);

SELECT * FROM composite_type_partitioned_table WHERE composite_column = '("key1", "20160101")'::new_composite_type;

UPDATE composite_type_partitioned_table SET id = 6 WHERE composite_column = '("key2", "20160101")'::new_composite_type;

SELECT * FROM composite_type_partitioned_table WHERE composite_column = '("key2", "20160101")'::new_composite_type;

其他說明：

有兩個注意事項要警惕：

>必須正确分隔輸入檔案以允許copy_to_distributed_table工作.為此,請使用COPY(SELECT():: composite_type_field,….);從普通表到檔案然後加載.>對于修剪以使用選擇查詢,複合類型字段應使用引号.