Back to prev

PostgreSQL In Action

Jan 25, 2021
Linkang Chan
@Jesse Chan

postgresql 是一款开源的对象关系数据库管理系统。pg 可以说是一款全栈的数据库，毕竟 slogan 是 “世界上最先进的关系型数据库”。同时 pg 比 mysql 少不少的坑，使用上会舒心一些。 本文主要是记录 pg 的使用方式，同时也会记录一些 sql 的语句或者 pg 独有的一些操作。

安装

  • Mac 安装
$ brew install postgresql@14
$ brew services start postgresql@14
$ brew services stop postgresql@14

Ⅰ 创建用户

pg 安装后默认会有一个postgres的用户，要创建新用户，需要先切换到该账户，然后执行如下的操作。

$ su - postgres
-bash-4.2$ psql
postgre=# create user username with password 'xxxxx';
postgre=# create database databaseName owner username;
postgre=# grant all privileges on database databaseName to username;

Ⅱ 登录数据库

使用用户名密码登录：

$ psql -h hosturl -U username -d databasename -p port

注意在一些编程的库中使用上面的方式可能会报sslmode required not support。通过增加sslmode=disable可以解决。

Ⅲ 导入导出

可以通过一些外部命令将 pg 数据库中的内容导出到一个 sql 文件中，同时也可以将这个 sql 文件重新应用到另外的数据库中。操作如下：

# export to dump.sql
$ pg_dump -h hosturl -U username databasename > /path/tp/save/dump.sql

# import to new database 
$ psql -h hosturl -d database -U username -f dump.sql

此外导出单独一个表的操作如下：

pg_dump -h localhost -p 5432 -U username --format plain --verbose \
   --file dump.sql --table public.tablename dbname

comment: 不过从高版本导出后，再导入到低版本时会有一些小错误。一般可以通过sed将对应的行注释掉。

Ⅳ 插入数据

values 可以指定多个，批量插入。

insert into table_name (column1, column2, column3) values (value1, value2, value3)[,(,...)];

Ⅴ 更新数据

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
RETURNING * | output_expression AS output_name;

RETURNING表示返回更新后符合条件的记录

Ⅵ 修改表格字段

# 增加字段
ALTER TABLE table_name 
ADD COLUMN column_name datatype column_constraint;

# 删除字段
ALTER TABLE table_name 
DROP COLUMN column_name;

# 重命名字段
ALTER TABLE table_name 
RENAME COLUMN column_name 
TO new_column_name;

# 改变默认值
ALTER TABLE table_name 
ALTER COLUMN column_name 
[SET DEFAULT value | DROP DEFAULT];

# 修改 not null 限制
ALTER TABLE table_name 
ALTER COLUMN column_name 
[SET NOT NULL| DROP NOT NULL];

# 增加check限制
ALTER TABLE table_name 
ADD CONSTRAINT constraint_name constraint_definition;

# 增加其他限制
ALTER TABLE table_name 
ADD CONSTRAINT constraint_name constraint_definition;

# 重命名表
ALTER TABLE table_name 
RENAME TO new_table_name;

修改列从 char 到 jsonb 格式

alter table table_name
  alter column column_name type jsonb using to_jsonb(column_name)

[alter table column]: 修改表的列的类型到 jsonb

导出查询内容到文件

一般先切换对应的用户，比如su postgres。 之后可以执行如下的命令：

psql -d models -c "COPY (select * from tblname) TO STDOUT WITH CSV HEADER" > a.csv

[参考回答]: 多种方式执行查询导出

如果是在进入到数据库内部后，可以通过

\copy (select * from tblname) TO 'filename.csv' With CSV DELIMITER ',' HEADER

查询 jsonb 字段

select body->'key' from table_name;

更新 jsonb 字段

update table_name
SET body = jsonb_set(body, '{some_key}', to_json('value'::text)::jsonb);

更新指定列的信息

ALTER TABLE saved_states ALTER COLUMN prev_months_access_counts TYPE jsonb
using       to_jsonb(prev_months_access_counts);

查询数据库的占用内存

单位：字节

select pg_database_size('database_name');

如果想看所有的数据库的大小：

SELECT datname,pg_database_size(datname) as size
FROM pg_database;

jsonb 解引用问题

  • @> 包含操作符

表示@>右边的内容是否全部在@>左边的内容中。

SELECT ARRAY[1, 2, 3] @> ARRAY[2, 3]; -- Returns true

-- json
SELECT * from table1 where jsoncolumn @> '{"key": value}';
  • @? 包含任意内容

表示@?左边的内容只要有一个在@?右边出现即可

SELECT ARRAY[1, 2, 3] @? ARRAY[3, 4]; -- Returns true
  • @@ 文本搜索操作

一般用于全文搜索，大部分是用于向量搜索，表示，右边的内容是否在左边的内容中出现。

SELECT to_tsvector('english', 'Hello world') @@ to_tsquery('english', 'world'); -- Returns true

[JSON Types]: 官方文档

查询活跃的进程

SELECT pid, state, backend_start, substr(query, 0, 100) q
FROM pg_stat_activity
WHERE backend_type = 'client backend' and query like '%users%'
ORDER BY backend_start;

按照用户进行删除

drop owned by the_user;