时间：2021-07-01 10:21:17 帮助过：6人阅读
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ
);
CREATE TABLE t_user (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
roles INT [] NOT NULL,
data JSONB,
created_at TIMESTAMPTZ
);
INSERT INTO t_role (id, name, created_at) VALUES (1, ‘超级管理员‘, now()), (2, ‘管理员‘, now()), (3, ‘用户‘, now());
INSERT INTO t_user(name, roles, data, created_at) VALUES
(‘root‘, ‘{1}‘, ‘{"email":"root@yangbajing.me"}‘, now()),
(‘羊八井‘, ‘{2,3}‘, ‘{"email":"yangbajing"}‘, now()),
(‘哈哈‘, ‘{3}‘, ‘{"email":"haha@yangbajing.me"}‘, now());
先来执行两个简单的 SELECT 查询：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
yangbajing=> select * from t_role; id | name | created_at ----+------------+------------------------------- 1 | 超级管理员 | 2018-02-01 22:03:17.168906+08 2 | 管理员 | 2018-02-01 22:03:17.168906+08 3 | 用户 | 2018-02-01 22:03:17.168906+08 (3 rows) yangbajing=> select * from t_user; id | name | roles | data | created_at ----+--------+-------+---------------------------------------+------------------------------- 2 | root | {1} | {"email": "root@yangbajing.me"} | 2018-02-01 22:06:21.140465+08 3 | 哈哈 | {3} | {"email": "haha@yangbajing.me"} | 2018-02-01 22:06:21.140465+08 1 | 羊八井 | {2,3} | {"email": "yangbajing@yangbajing.me"} | 2018-02-01 22:04:41.580203+08 (3 rows) |
接下来，尝试一些 PG 特色特性。
插入或更新，是一个很有用的特性，当在主键冲突时可以选择更新数据。在PG中，是使用 ON CONFLICT 来实现这个特性的。
|
1 2 3 4 |
INSERT INTO t_role (id, name, created_at) VALUES (3, ‘普通用户‘, now()) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; |
在常用的 INSERT 语句后面用 ON CONFLICT (...) DO .... 语句来指定在某个/些字段出现冲突时需要执行的语句。在 on CONFLICT (...) 里的参数需要是主键或唯一索引（可以为复合字段）。当冲突发生时则会执行 DO .... 后面的语句，这里我们选择更新 name 字段的值。EXCLUDED 是用户引用在 VALUES .... 部分我们将插入的数据，EXCLUDED.name 在这里就是 ‘普通用户‘ 。除 DO UPDATE，我们还可以使用 DO NOTHING 来简单的忽略插入时的主键冲突。
看看表 t_user 的结构：
|
1 2 3 4 5 6 7 8 9 10 11 |
yangbajing=> \d t_user Table "public.t_user" Column | Type | Collation | Nullable | Default ------------+--------------------------+-----------+----------+------------------------------------ id | bigint | | not null | nextval(‘t_user_id_seq‘::regclass) name | character varying(255) | | not null | roles | integer[] | | not null | data | jsonb | | | created_at | timestamp with time zone | | | Indexes: "t_user_pkey" PRIMARY KEY, btree (id) |
在建表时 id 字段的类型定义的是 BIGSERIAL ，但这里却是显示的 bigint 类型；另外，还多了一个默认值：nextval(‘t_user_id_seq‘::regclass) 。这是 PG 中的 序列 ，PG中使用序列来实现 自增值 的特性。
序列：t_user_id_seq
|
1 2 3 4 5 6 |
yangbajing=> \d t_user_id_seq Sequence "public.t_user_id_seq" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache --------+-------+---------+---------------------+-----------+---------+------- bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 Owned by: public.t_user.id |
也可以先创建序列，再设置字段的默认值为该序列的下一个值。
|
1 |
CREATE SEQUENCE t_user_id2_seq INCREMENT BY 1 MINVALUE 1 START WITH 1; |
这里创建一个序列，设置最小值为1，从1开始按1进行递增。
在创建 t_user 表的 roles 字段时，使用了数组类型 INT [] 。数组类型对于我们的数据建模来说很有用，使用得好可以大大减少关系表的数量。
根据索引返回值
|
1 2 3 4 5 6 7 |
yangbajing=> SELECT id, name, roles[2], created_at FROM t_user; id | name | roles | created_at ----+--------+-------+------------------------------- 2 | root | | 2018-02-01 22:06:21.140465+08 3 | 哈哈 | | 2018-02-01 22:06:21.140465+08 1 | 羊八井 | 1 | 2018-02-01 22:04:41.580203+08 (3 rows) |
注意：PG 中，索引下标从0开始
以行的形式输出数组元素
|
1 2 3 4 5 6 7 8 |
yangbajing=> SELECT id, unnest(roles) AS role_id FROM t_user; id | role_id ----+--------- 2 | 1 3 | 3 1 | 2 1 | 1 (4 rows) |
包含查找
|
1 2 3 4 5 |
yangbajing=> SELECT * FROM t_user WHERE roles @> ARRAY[1,2]; id | name | roles | data | created_at ----+--------+-------+---------------------------------------+------------------------------- 1 | 羊八井 | {2,1} | {"email": "yangbajing@yangbajing.me"} | 2018-02-01 22:04:41.580203+08 (1 row)
|
PostgreSQL入门，PostgreSQL和mysql
标签：vcc 数据库管理 lis domain data -- 完整性 路径 退出