pgsql使用

参考文档：http://www.postgres.cn/docs/10/

---

安装：

https://www.myfreax.com/how-to-install-postgresql-on-ubuntu-18-04/

进入pgsql

sudo -u postgres psql 

##执行脚本
echo 'sudo PGPASSWORD=pass_2021 psql -h 127.0.0.1 -p 5432 -U nft' > pgsql.sh
##给权限
sudo chmod 777 pgsql.sh
##执行
./pgsql.sh

备份数据库

pg_dump -h 127.0.0.1 -U nft -d nft -p 5434 -f /tmp/nft.sql

创建用户和数据库

create user xicheng createdb createrole login password 'pass_word';


create user gc createdb createrole login password 'pass_word';

#切换成xicheng用户运行再创建 数据库

docker run -it --rm dencold/pgcli postgresql://xicheng:pass_2020@172.16.0.10:5432/postgres

CREATE DATABASE xicheng;

切换数据库

 psql -U xicheng -d xicheng 


 psql -U gc -d gc

查看数据库

\l

更改数据库角色

alter database xicheng owner to xicheng;

修改表名

ALTER TABLE 表名 ADD 字段 类型;

删除视图/表

drop view/table 表名/视图名

增加字段

ALTER TABLE lease_order ADD COLUMN lender_meta jsonb NOT NULL DEFAULT '{}';


ALTER TABLE pay_order ADD COLUMN out_trade_no text NOT NULL DEFAULT '';

修改字段类型

 alter table account_balance alter COLUMN freeze_id type text ;

更改数据表/视图角色

DO $$DECLARE r record;
BEGIN
FOR r IN SELECT tablename/viewname FROM pg_tables/pg_views WHERE schemaname = 'public'
LOOP
    EXECUTE 'alter table '|| r.tablename/r.viewname ||' owner to new_owner;';
END LOOP;
END$$;

DO $$DECLARE r record;
BEGIN
FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public'
LOOP
    EXECUTE 'alter table '|| r.viewname ||' owner to wallet;';
END LOOP;
END$$;

DO $$DECLARE r record;
BEGIN
FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
LOOP
    EXECUTE 'alter table '|| r.tablename ||' owner to wallet;';
END LOOP;
END$$;

创建视图

---car_model_view
CREATE VIEW car_model_view AS
SELECT cm.id AS car_model_id,cm.name as car_model_name,cm.remark as car_model_remark,cb.car_vender_id,cb.id as car_brand_id,
cb.brand_name as car_brand_name,cb.logo as car_brand_logo
FROM car_model cm LEFT JOIN car_brand cb
ON cm.car_brand_id = cb.id;

清空表数据保留表结构

//当表没有其他关系时
TRUNCATE TABLE tablename;


//当表中有外键时，要用级联方式删所有关联的数据
TRUNCATE TABLE tablename CASCADE;

数据库备份

1、从一台机子复制到另外一台机子

scp car.sql ubuntu@192.168.1.5:/tmp/car.sql

2、pgsql导入数据表

(1)直接psql命令执行SQL文件

psql -h 172.16.0.10 -d xicheng -U xicheng -f /tmp/car.sql;

(2)连接db后执行sql文件

#因为这里是容器运行，所以需要将宿主机文件加载进容器，-v是目录挂载实现宿主机与容器机子目录互通


#连接  -v是映射卷挂载目录
docker run -it -v /tmp:/tmp --rm dencold/pgcli postgresql://xicheng:pass_2020@172.16.0.10:5432/xicheng


#进入数据库执行文件
\i /tmp/car.sql;

更新jsonb字段

update account_profile set id_img_url='{}', driving_licence_img_url='{}'  where account_id = '229535169685297152';

随机数

SELECT floor(random()*(25-10)+10);