豪翔天下

Change My World by Program

0%

PostgreSQL 使用手册

最近在学习AWS的Redshift,它是基于PostgreSQL的,顺便学习下。

安装PostgreSQL

配置

  • 默认端口为5432,默认用户名为postgres

系统表

  • pg_class: 记录数据库中的表,索引及视图
  • pg_namespace: 记录数据库中的名字空间
  • pg_attribute: 记录数据库中表的字段的详细信息。(attname字段名字,atttypid字段类型id,attlen字段长度,attnotnull)
  • pg_type: 记录数据库中所有的数据类型
  • pg_description: 记录数据库中对象的注释(表以及字段)。(objoid对象ID,objsubid字段号,description描述)

索引

  • Redshift不能像PostgreSQL那样创建索引,但是提供了一个类似的功能sort key,该功能可以和传统数据库一样能够优化存储与查询方面的性能

系统管理

1
2
3
4
5
6
7
8
9
SELECT version();	# 获取数据库版本

# sudo apt-get install postgresql-client,命令行得先安装客户端,但是客户端的版本必须和服务端的版本一致,这就很麻烦了
psql -U postgres -h xxx -w --password # 使用命令行登录postgres数据库

pg_dump -U username your_database > db_dump.bak # 备份数据库

SELECT * FROM pg_stat_activity; # 检查当前有哪些session,哪些连接
select pg_terminate_backend(pid) from pg_stat_activity; # 删除某个session

增删该查

数据库操作

1
2
3
4
5
6
7
8
9
10
11
# postgres shell中执行
\list # 列出当前的数据库
\c 数据库名 # 切换数据库
\dt # 列出当前的表

DROP DATABASE name; # 删除数据库

# 如果是sql语句,必须加分号,且关键字必须大写
CREATE DATABASE 数据库名; # 创建数据库,那些
CREATE USER 用户名 WITH ENCRYPTED PASSWORD '密码'; # 创建用户
GRANT ALL PRIVILEGES ON DATABASE 数据库名 TO 用户名; # 给用户分配某个数据库的权限

数据表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 使用表名查询表字段的定义
SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = '表名'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum

# 添加注释,在创建的时候不能添加,只能用不同的语句加注释
COMMENT ON TABLE users IS "This is user table" # 给表添加注释
COMMENT ON COLUMN users.userid IS 'This is user ID'; # 给表字段添加注释

ALTER TABLE test DROP COLUMN name; # 删除字段
ALTER TABLE test ADD COLUMN name VARCHAR(255); # 添加字段

数据操作

查询记录

1
2
3
4
5
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;	# 分页操作,limit操作
SELECT * FROM sfdc_contact WHERE "last_name"='Acevedo' AND "first_name"='Antonio'; # 字段名用双引号,字段值用单引号

# json字段查询
SELECT * FROM users, jsonb_array_elements(users.data) r WHERE r->>'name' = 'value'; # 如果users表的data字段是一个数组,可以用这种方式查询数组的key value

新增记录

更新记录

删除记录

事务

1
2
3
BEGIN;
other sql;
COMMIT;

TroubleShooting

  • ProgrammingError: Statement is too large. Statement Size: 40000000 bytes. Maximum Allowed: 16777216 bytes: 这是因为PostgreSQL默认设置最大的sql语句为16M,所以尽量一条语句的大小尽量控制在这之下

  • Permission denied for relation: 那是因为该用户没有表的访问权限,可以这样做:

    1
    2
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to test;	# 分配所有公共表权限
    GRANT ALL PRIVILEGES ON TABLE table-name TO test; # 分配指定表的权限
  • Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey”
    尝试执行以下命令重置一下主见索引,原理见stackoverflow

    1
    SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;
  • must appear in the GROUP BY clause or be used in an aggregate function(列必须出现在group by子句中或聚合函数中): postgresql一个非常常见的问题,常常在使用聚合函数的时候出现,例如count。没有啥一劳永逸的解决方法,反正遇到这种问题,它让你加哪个列到group by里面就加进去吧,一定要看看sql的输出结果,看看是不是自己想要的。(这个才是sql的标准,mysql没有完全执行。。。)

扩展阅读

PostgreSQL 9.6.0中文手册

坚持原创技术分享,谢谢支持

欢迎关注我的其它发布渠道