Upgrade postgres in Docker
Upgrade postgres from v14.5 to v15.1
如何升级 docker 中的 postgres 版本?
Backup
Use beblow command, replace yourService
with yourself container_name.
1docker-compose exec yourService pg_dumpall -U postgres > 14.5.backup
Modify your postgres tag by using "15" instead of "14", then down your container.
1docker-compose rm -s -v yourService
Boot the new DB - PostgreSQL 15.1 and Restore from backup
1docker-compose up -d yourService
2cat 14.5.backup | docker-compose exec -T db psql -U postgres
关于 postgres 的一些介绍
JSON 和 JSONB 的区别
postgresql支持两种json数据类型:json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json类型存储快,使用慢,jsonb类型存储稍慢,使用较快。
注意:键值对的键必须使用双引号
从PostgreSQL 9.3开始,json就成了postgres里的一种数据类型,也就是和varchar、int一样,我们表里的一个字段的类型可以为json了。
与此同时,postgres还提供了jsonb格式,jsonb格式是json的二进制形式,二者的区别在于json写入快,读取慢,jsonb写入慢,读取快,但在操作上,二者是没有区别的。
查询数据
Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:
1select info from name_age where info @> '{"id":1}'::jsonb
用到了 @>
这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。
再来一个复杂一点的查询的,查询 age>16
的记录,并且只显示 name
,语句如下:
1select info->'name' from name_age where (info->>'age')::int4 > 16
关于详细运算符使用,请参考官方文档: 9.15. JSON Functions and Operators
修改数据
下面,将 age
从 18
改为 22
,SQL语句:
1SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1
上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。
除了操作符以外,还可以使用函数操作: jsonb_set()
,函数签名如下:
1jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
详细使用可参考 9.15. JSON Functions and Operators
删除数据
删除age这个key,SQL如下:
1SELECT info-'age' from name_age where (info->>'id')::int4 = 1
直接用操作符 -
即可。
总结
PostgreSQL 9.5以上的版本中有了很多方便的操作符,使得操作json变得非常方便了。
json和jsonb的操作符
操作符 | 右操作数类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
-> | int | 获取JSON数组元素(索引从0开始) | select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2; | {"c":"baz"} |
-> | text | 通过键获取值 | select '{"a": {"b":"foo"}}'::json->'a'; | {"b":"foo"} |
->> | int | 获取JSON数组元素为 text | select '[1,2,3]'::json->>2; | 3 |
->> | text | 通过键获取值为text | select '{"a":1,"b":2}'::json->>'b'; | 2 |
#> | text[] | 在指定的路径获取JSON对象 | select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}'; | {"c": "foo"} |
#>> | text[] | 在指定的路径获取JSON对象为 text | select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'; | 3 |
jsonb额外操作符
操作符 | 右操作数类型 | 描述 | 示例 | 结果 |
---|---|---|---|---|
@> | jsonb | 左侧json最上层的值是否包含右边json对象 | select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb; select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; | f;t |
<@ | jsonb | 左侧json对象是否包含于右侧json最上层的值内 | select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb; | t |
? | text | text是否作为左侧Json对象最上层的键 | select '{"a":1, "b":2}'::jsonb ? 'b'; | t |
? | text[] | text[]中的任一元素是否作为左侧Json对象最上层的键 | select '{"a":1, "b":2, "c":3}'::jsonb ? | |
?& | text[] | text[]中的所有元素是否作为左侧Json对象最上层的键 | select '["a", "b"]'::jsonb ?& array['a', 'b']; | t |
jsonb | 连接两个json对象,组成一个新的json对象 | |||
- | text | 删除左侧json对象中键为text的键值对 | select '{"a": "b"}'::jsonb - 'a'; | {} |
- | integer | 删除数组指定索引处的元素,如果索引值为负数,则从右边计算索引值。如果最上层容器内不是数组,则抛出错误。 | select '["a", "b"]'::jsonb - 1; | ["a"] |
#- | text[] | 删除指定路径下的域或元素(如果是json数组,且整数值是负的,则索引值从右边算起) | select '["a", {"b":1}]'::jsonb #- '{1,b}'; | ["a", {}] |
不要信任 PostgreSQL 的默认排序
MYSQL,PG 是没有默认排序的, 不指定排序返回顺序不可靠
在 PostgreSQL 中创建一个只读用户
1. 创建新用户
创建一个新用户(例如,readonly_user
)并为其设置密码:
1CREATE USER readonly_user WITH ENCRYPTED PASSWORD 'your_password';
2. 赋予只读权限
对于您希望只读用户访问的每个表,执行以下命令以赋予只读权限:
1GRANT USAGE ON SCHEMA public TO readonly_user;
2GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
如果您以后在 public
模式下添加了更多表,您还需要为这些新表赋予只读权限。您可以通过以下命令自动完成这一操作:
1ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
请注意,此命令仅适用于在执行此命令之后创建的表。对于已存在的表,您仍然需要使用 GRANT SELECT
命令。
PostgreSQL 如何 order by 多个字段
在 PostgreSQL 中,您可以通过在 ORDER BY
子句中指定多个字段来实现按照多个字段排序。您只需在字段名称之间用逗号隔开即可。以下是一个示例:
1SELECT *
2FROM your_table
3ORDER BY column1, column2;
在此查询中,数据首先按 column1
排序,然后在 column1
相同的行中按 column2
排序。默认情况下,排序顺序为升序(ASC),如果你想改为降序(DESC),可以这样指定:
1SELECT *
2FROM your_table
3ORDER BY column1 DESC, column2 DESC;
你也可以针对不同字段使用不同的排序顺序,如下所示:
1SELECT *
2FROM your_table
3ORDER BY column1 ASC, column2 DESC;
在这个例子中,数据首先按 column1
升序排序,然后在 column1
相同的行中按 column2
降序排序。
在 TypeORM 中的写法
在 TypeORM 中,你可以在 orderBy
选项中指定多个字段进行排序。例如:
1const results = await repository.find({
2 order: {
3 column1: 'ASC',
4 column2: 'DESC',
5 },
6});
在这个例子中,column1
以升序排序,column2
以降序排序。
这里的 repository
是你要查询的实体的仓库,你可以通过 getRepository
函数或者直接注入(如果你在 Nest.js 或者其他支持依赖注入的框架中使用)来获取。
请注意你的实体需要有对应的 column1
和 column2
字段,否则 TypeORM 将无法执行此查询。
涉及大小写问题,需要使用引号包起来
1const results = await repository.find({
2 order: {
3 '"publishYear"': 'ASC',
4 },
5});