postgres 基础操作

目录

1. 登录远程数据库 

2. 数据库内的基本操作

3. 增删改查

4. 导出和导入sql格式数据

5. 导入dump格式数据

6. 批量删除数据表

1. 登录远程数据库 

$ psql -h pinpoint.c3iy4men5.us-east-1.rds.amazonaws.com -U postgres -d pinpoint 
 
// 退出postgres
\q

2. 数据库内的基本操作

// 删除数据库
=> drop database pinpoint;

// 删除有active connections的数据库
// https://www.postgresqltutorial.com/postgresql-drop-database/

// 创建数据库
=> create database pinpoint;

// 切换数据库
=> \c db_name

// 列出所有数据库
=> \l

// 列出当前数据库的所有schema
=> select schema_name from information_schema.schemata;
      schema_name
-----------------------
 information_schema
 pg_catalog
 pg_toast_temp_1
 pg_temp_1
 Tmp
 public
 SurveyForm
(7 rows)

// 列出当前数据库的“默认Schema中的表”、“所有表”、“某个Schema中的表”
=> \dt
=> \dt *.*
=> \dt "SurveyForm".*

// 查看数据库最大连接数
=> SHOW max_connections;
 max_connections
-----------------
 193
(1 row)


// 删除数据表
=> DROP TABLE table_name;

// 帮助
=> \h

// 退出
=> \q

3. 增删改查

// 修改某个字段的值
UPDATE "HealthSystem"."Alerts" SET "emailReceivers" = '{worldzhy@126.com}' WHERE TRUE;

// 字符串替换
UPDATE "CronJob"."CronJobs" SET "callback" = REPLACE("callback", 'https://app', 'https://qa');

// 将String字段的值，复制到String[]字段
UPDATE public."Task" SET "assigneeUserIds"=string_to_array("assigneeUserId",'');

4. 导出和导入sql格式数据

// 导出
$ pg_dump -O --inserts -h pinpoint.c3y4mtmn5.us-east-1.rds.amazonaws.com -U postgres pinpoint > db.sql

// 导入
$ psql --dbname="pinpoint" --file="./db.sql" --username="postgres" --single-transaction -W -h pinpoint.c3y4mtmn5.us-east-1.rds.amazonaws.com

$ psql -d pinpoint --file="./db.sql" -U postgres --single-transaction -W -h pinpoint.c3y4mtmn5.us-east-1.rds.amazonaws.com

5. 导入dump格式数据

// 如果需要将原数据清空，加--clean参数。
$ pg_restore --username=postgres  --dbname=healthcomps --jobs=4 --verbose [--clean] healthcomps_staging_backup_2018-11-20.dump

6. 批量删除数据表

CREATE FUNCTION del_table() RETURNS void AS $$
DECLARE
    tmp VARCHAR(512);
DECLARE names CURSOR FOR 
    select tablename from pg_tables where schemaname='public';
BEGIN
  FOR stmt IN names LOOP
    tmp := 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
    RAISE NOTICE 'notice: %', tmp;
    EXECUTE 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
  END LOOP;
  RAISE NOTICE 'finished .....';
END;
 
$$ LANGUAGE plpgsql;

--执行函数批量删除表
select del_table();