postsql 相关操作

发表于 | | 访客

postgresql 数据库 相关 操作

创建数据库

1
create database anser_test1 with owner=anser encoding='utf8';

授权用户访问

1
grant all on database anser_test1 to anser;

导入sql文件

1
psql -U anser -d anser_test1 -f anser_sql_bak/anser-20170816-1028.sql

替换json格式的数据

1
update tag set tags = replace(tags::TEXT, '"BU"','"bu"')::jsonb; ##把所有BU修改为bu

查询json格式数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
k8s=# select id,name,instance_id, labels, pool_id from k8s_pod where deleted=0 and labels ->>'app'='web_server';
       id        |            name             |             instance_id              |                 labels           
      |    pool_id    
-----------------+-----------------------------+--------------------------------------+----------------------------------
------+---------------
 k8spod-zkac2ty0 | test-7449b45986-tvjhl | 8e2cdebe-a443-11e8-8f5f-fa163e264c06 | {"app": "web_server"}            
      | pool-bjxgk8s1
 k8spod-eecfm8qm | test-7449b45986-d7jjr | 8e2c7ba7-a443-11e8-8f5f-fa163e264c06 | {"app": "web_server", "name": "Layne"}       | pool-bjxgk8s1 
k8s=# select id,name,instance_id, labels, pool_id from k8s_pod where deleted=0 and labels ->>'app'='web_server' and labels ->> 'name'='Layne';
       id        |            name             |             instance_id              |                 labels           
      |    pool_id    
-----------------+-----------------------------+--------------------------------------+----------------------------------
------+---------------
 k8spod-eecfm8qm | test-7449b45986-d7jjr | 8e2c7ba7-a443-11e8-8f5f-fa163e264c06 | {"app": "web_server", "name": "La
yne"} | pool-bjxgk8s1

修改字段类型

1
alter table storage alter column port type character varying(50);