简单的写入数据和查询
　　sharddb=# INSERT INTO customer_reviews (customer_id， review_rating) VALUES ('HN802'， 5);
　　sharddb=# INSERT INTO customer_reviews VALUES ('HN802'， '2004-01-01'， 1， 10， 4， 'B00007B5DN'， 'Tug of War'， 133191， 'Music'， 'Indie Music'， 'Pop'， '{}');
　　sharddb=# INSERT INTO customer_reviews (customer_id， review_rating) VALUES ('FA2K1'， 10);
　　无WHERE子句的SELECT
　　sharddb=# select * from customer_reviews ;
　　customer_id | review_date | review_rating | review_votes | review_helpful_votes | product_id | product_title | product_sales_rank | product_group | product_category | product_subcategory | similar_product_ids
　　----------{}----------------{}------------------------{}---------------{}-----------------------{}---------------------------+------------------
　　HN802 | | 5 | | | | | | | | |
　　HN802 | 2004-01-01 | 1 | 10 | 4 | B00007B5DN | Tug of War | 133191 | Music | Indie Music | Pop | {}
　　FA2K1 | | 10 | | | | | | | | |
　　(3 rows)
　　无WHERE子句的avg
　　sharddb=# SELECT avg(review_rating) FROM customer_reviews;
　　avg
　　--------------------
　　5.3333333333333333
　　(1 row)
　　带有GROUP BY子句的avg
　　sharddb=# SELECT customer_id，avg(review_rating) from customer_reviews GROUP BY customer_id;
　　customer_id | avg
　　----------+------------------
　　FA2K1 | 10.0000000000000000
　　HN802 | 3.0000000000000000
　　(2 rows)
　　带有HAVING子句的avg
　　sharddb=# SELECT customer_id，avg(review_rating) as avgrating from customer_reviews GROUP BY customer_id HAVING customer_id <> 'FA2K1';
　　customer_id | avgrating
　　----------+-----------------
　　HN802 | 3.0000000000000000
　　(1 row)
　　无WHERE子句的avg
　　sharddb=# SELECT avg(review_rating) FROM customer_reviews WHERE customer_id = 'HN802';
　　avg
　　--------------------
　　3.0000000000000000
　　(1 row)
　　COUNT ， NULL值
sharddb=# SELECT count(*) FROM customer_reviews;
count
-------
3
(1 row)
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes <> 4;
count
-------
0
(1 row)
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes = 4;
count
-------
1
(1 row)
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes IS NULL;
count
-------
2
(1 row)
^
sharddb=# SELECT count(*) FROM customer_reviews WHERE review_helpful_votes IS NOT NULL;
count
-------
1
(1 row)
　　带有分区条件列的UPDATE操作
　　sharddb=# UPDATE customer_reviews SET review_votes = 10 WHERE customer_id = 'HN802';
　　UPDATE 2
　　sharddb=#
　　不带分区条件列的UPDATE操作：
　　sharddb=# UPDATE customer_reviews SET review_votes = 10 + 1 WHERE review_votes = 10;
　　ERROR: cannot modify multiple shards during a single query
　　sharddb=#
　　不带分区条件列的DELETE操作：
　　sharddb=# DELETE FROM customer_reviews WHERE review_votes <> 99;
　　ERROR: cannot modify multiple shards during a single query
　　sharddb=#
　　带有分区条件列和其他列的UPDATE操作
　　sharddb=# UPDATE customer_reviews SET review_votes = 10 + 1 WHERE customer_id = 'HN802' AND review_votes = 10;
　　UPDATE 2
　　sharddb=#
　　管理工具
　　pgs_distribution_metadata SCHEMA master节点用来存放元数据
sharddb=# dn+
List of schemas
Name | Owner | Access privileges | Description
------------------------{}--------------------+---------------------
pgs_distribution_metadata | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
sharddb=# SELECT * FROM pgs_distribution_metadata.partition;
relation_id | partition_method | key
----------{}----------------------
24842 | h | customer_id
(1 row)
sharddb=# SELECT * FROM pgs_distribution_metadata.shard;
id | relation_id | storage | min_value | max_value
----{}----------{}-----------------
10000 | 24842 | t | -2147483648 | -1879048194
10001 | 24842 | t | -1879048193 | -1610612739
10002 | 24842 | t | -1610612738 | -1342177284
10003 | 24842 | t | -1342177283 | -1073741829
10004 | 24842 | t | -1073741828 | -805306374
10005 | 24842 | t | -805306373 | -536870919
10006 | 24842 | t | -536870918 | -268435464
10007 | 24842 | t | -268435463 | -9
10008 | 24842 | t | -8 | 268435446
10009 | 24842 | t | 268435447 | 536870901
10010 | 24842 | t | 536870902 | 805306356
10011 | 24842 | t | 805306357 | 1073741811
10012 | 24842 | t | 1073741812 | 1342177266
10013 | 24842 | t | 1342177267 | 1610612721
10014 | 24842 | t | 1610612722 | 1879048176
10015 | 24842 | t | 1879048177 | 2147483647
(16 rows)
sharddb=# SELECT * FROM pgs_distribution_metadata.shard_placement;
id | shard_id | shard_state | node_name | node_port
--------------{}-------------
1 | 10000 | 1 | localhost | 5433
2 | 10000 | 1 | localhost | 5434
......
32 | 10015 | 1 | localhost | 5434
(32 rows)
　　增加表，但先写几条数据再做shard，会有以下几个严重的错误，所以一定要遵循创建表->做shard-->写数据，否则在做shard之前写入的数据都处于不可见的状态，而且毫无提示：
　　1)不能自动重新分发数据
　　2)在worker nodes中并没有成功创建表，而且没有错误提示
　　3)master节点查询所有shard，还有customer_detail表的信息（releation_id=24940），实际上，在drop掉这张表后，在pg系统表中该表已经被删除了
（master）
sharddb=# INSERT INTO customer_detail VALUES ('HN802'，'a')，('HN802'，'b')，('FA2K1'，'c');
INSERT 0 3
sharddb=#
sharddb=# SELECT master_create_distributed_table('customer_detail'， 'customer_id');
master_create_distributed_table
---------------------------------
(1 row)
sharddb=#
sharddb=# SELECT master_create_worker_shards('customer_detail'， 16， 2);
master_create_worker_shards
-----------------------------
(1 row)
sharddb=#
sharddb=#
sharddb=# select * from customer_detail ;
customer_id | customer_val
----------+-----------
(0 rows)
（worker nodes）
sharddb=# drop table customer_detail;
ERROR: table "customer_detail" does not exist
sharddb=#
（master）
sharddb=# SELECT * FROM pgs_distribution_metadata.shard;
id | relation_id | storage | min_value | max_value
----{}----------{}-----------------
10000 | 24842 | t | -2147483648 | -1879048194
......（略）
10031 | 24940 | t | 1879048177 | 2147483647
(32 rows)
sharddb=# CREATE TABLE tbl_detail(customer_id text， fid integer ， detailval text);
CREATE TABLE
sharddb=#
sharddb=#
sharddb=# SELECT master_create_distributed_table('tbl_detail'， 'customer_id');
master_create_distributed_table
---------------------------------
(1 row)
sharddb=# SELECT master_create_worker_shards('tbl_detail'， 16， 2);
master_create_worker_shards
-----------------------------
(1 row)
sharddb=#
sharddb=#
sharddb=# select customer_id from customer_reviews ;
customer_id
-------------
HN802
HN802
FA2K1
(3 rows)
　　插入测试数据，不能使用如下语法批量插入，只能一行一行的插入
sharddb=# INSERT INTO tbl_detail VALUES('HN802'，1，'a')，('HN802'，2，'b')，('HN802'，3，'c')，('FA2K1'，4，'d');
ERROR: cannot perform distributed planning for the given query
DETAIL: Multi-row INSERTs to distributed tables are not supported.
sharddb=#
sharddb=# INSERT INTO tbl_detail VALUES('HN802'，1，'a');
INSERT 0 1
sharddb=# INSERT INTO tbl_detail VALUES ('HN802'，2，'b');
INSERT 0 1
sharddb=# INSERT INTO tbl_detail VALUES ('HN802'，3，'c');
INSERT 0 1
sharddb=# INSERT INTO tbl_detail VALUES ('FA2K1'，4，'d');
INSERT 0 1
sharddb=#
sharddb=#
sharddb=#
sharddb=# SELECT * FROM tbl_detail ;
customer_id | fid | detailval
----------{}-----------
HN802 | 1 | a
HN802 | 2 | b
HN802 | 3 | c
FA2K1 | 4 | d
(4 rows)
　　简单的join测试
　　sharddb=#
　　sharddb=# SELECT A.*，B.* FROM customer_reviews A， tbl_detail B WHERE A.customer_id = B.customer_id;
　　ERROR: cannot perform distributed planning for the given query
　　DETAIL: Joins are not supported in distributed queries.
　　sharddb=#
　　无法查看EXPLAIN，这是个硬伤，同时，VACUUM 、 ANALYZE 也需要单独在每个worker操作。
　　sharddb=# EXPLAIN SELECT * FROM tbl_detail ;
　　ERROR: EXPLAIN commands on distributed tables are unsupported
　　sharddb=#
　　在MASTER上创建索引，在其他worker上都是不能同步的，DROP一样对worker无效
　　sharddb=# CREATE INDEX CONCURRENTLY ON tbl_detail (customer_id);
　　CREATE INDEX
　　sharddb=# d+ tbl_detail
　　Table "public.tbl_detail"
　　Column | Type | Modifiers | Storage | Stats target | Description
　　----------{}----------{}------------+----------
　　customer_id | text | | extended | |
　　fid | integer | | plain | |
　　detailval | text | | extended | |
　　Indexes:
　　"tbl_detail_customer_id_idx" btree (customer_id)
　　sharddb=#
　　sharddb=# DROP INDEX tbl_detail_customer_id_idx;
　　DROP INDEX
　　sharddb=#
　　ALTER TABLE不会抛出错误，但是如果不在其他节点做同样操作将无法再正确的读取数据
　　sharddb=# ALTER TABLE tbl_detail ADD COLUMN newcolumn text DEFAULT NULL;
　　ALTER TABLE
　　sharddb=# select * from tbl_detail ;
　　WARNING: Bad result from localhost:5434
　　DETAIL: Remote message: column "newcolumn" does not exist
　　WARNING: Bad result from localhost:5433
　　DETAIL: Remote message: column "newcolumn" does not exist
　　ERROR: could not receive query results
　　sharddb=#
　　DROP DATABASE 需要注意顺序
　　在master节点存在sharddb时，在worker删除database时会报出错误，DROP掉MASTER节点上的对象后，才可以手动删除对象：
　　postgres=# DROP DATABASE sharddb;
　　ERROR: database "sharddb" is being accessed by other users
　　DETAIL: There is 1 other session using the database