PostgreSQL Cookie

创建新的Schema

1
2
3
create schema postgres_survey;

comment on schema postgres_survey is 'postgres特性调研';

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table postgres_survey.array_samp
(
id bigint
constraint array_samp_pk
primary key,
classifies bigint[] not null,
role_matrix text[][] not null
);

comment on table postgres_survey.array_samp is 'Array类型调研';

comment on column postgres_survey.array_samp.id is '主键';

comment on column postgres_survey.array_samp.classifies is '分类ID数组';

comment on column postgres_survey.array_samp.role_matrix is '权限矩阵';

添加记录

1
2
3
4
5
6
insert into array_samp(id, classifies, role_matrix)
values ('1', '{10, 11}', '{{"r","w","x"}}');

insert into array_samp(id, classifies, role_matrix)
values ('2', '{13, 14}', '{ {"r","w","x"}, {"r","w","x"}, {"r","w","x"}}');

Upsert用法

1
2
3
4
5
6
7
8
9
10

--- 如果ID重复不做任何操作
insert into array_samp(id, classifies, role_matrix)
values ('3', '{10,11}', '{{"r","w","x"}}')
on conflict (id) do nothing ;
--- 如果ID重复更新 classifies和role_maxtirx字段
insert into array_samp(id, classifies, role_matrix)
values ('3', '{10,13}', '{{"r","w","9"}}')
on conflict (id) do update set "classifies" = excluded.classifies,
"role_matrix" = excluded.role_matrix;