Postgresql 入门笔记

引言

 

 　　最近整理了一些PostgreSQL的 常用命令，仅供参考

 

1. 入门命令

　　

# 重启数据库
$ service postgresql-9.5 restart

# 登陆：
$ psql -h 172.16.35.179 -U username -d dbname

# 切换数据库
$ \c dbname username

# 列出所有的数据库
$ \l或\list

# 切换数据库
$ \c dbname

# 列出当前数据库下的数据表
$ \d

# 列出指定表的所有字段
$ \d tablename

# 查看指定表的基本情况
$ \d+ tablename

# 退出登录
$ \q

# 查看索引
$ \di

# 导入sql
$psql -U postgres demo</目录/1.sql

 

2. 常用函数：

 

　　1. 时间戳函数

 

　　# 时间字符传转时间戳
　　select extract(epoch FROM date('2018-04-11 23:59:59')); # 1523404800

　　# 时间戳转时间字符串
　　select date(to_timestamp(1523404800))

　　# 时间戳转字符串
　　select to_char(to_timestamp(1523404800), 'YYYY-MM-dd HH24:MI:SS')

　　# 转换数值类型
　　CAST(t3.userid AS text) # int NUMERIC VARCHAR 也可以

　　# 默认值
　　coalesce(null, 1); # 1

 

　　2. 数值函数

 

　　AVG() 返回某列的平均值

　　COUNT() 返回某列的行数

　　MAX() 返回某列的最大值

　　MIN() 返回某列的最小值

　　SUM() 返回某列的值之和

 

　　3. 常用的字符串函数

 

　　LENGTAH(s) 计算字符串长度

　　CONCAT(s1,s2,……) 字符串合并函数

　　LETRIM(s)/RTRIM(s)/TRIM(s) 删除字符串空格函数

　　REPLACE(s,s1,s2) 字符串替换函数

　　SUBSTRING(s,n,len) 获取子串函数

 

　　4. 常用的日期和时间函数

 

　　EXTRACT(type FROM d) 获取日期指定值函数

　　CURRENT_DATE 获取当前日期函数

　　CURRENT_TIME 获取当前时间

 

3. 目录结构介绍

 　　附参考文章：https://blog.csdn.net/Linzhongyilisha/article/details/78275690

 

　　1. 安装目录的结构：/usr/local/pgsql

　　　　bin            二进制可执行文件目录

　　　　include      头文件目录

　　　　lib             动态库目录

　　　　share         文档和配置模板文件

　　2. 数据目录的结构 

　　　　一般使用PGDATA指向数据目录的根目录。这个目录是在安装时指定的

　　　　目录的初始化使用initdb来完成，完成后（pgsql根目录或者/var/lib/pgsql/10/data）下就会生成3个配置文件

　　　　postgresql.conf         数据库实例的主配置文件，基本上所有的配置参数都在此文件中；

　　　　pg_hba.conf             认证配置文件，配置了允许哪些ip访问数据库，认证的方法是什么等信息；

　　　　pg_ident.conf           认证方式的用户映射文件。

　　3. 此目录下还有一些子目录：

　　　　base　　　　　　　　默认表空间目录

　　　　global                      一些共享系统表的目录

　　　　pg_clog                    commit log的目录

　　　　pg_log                     系统日志目录，在查询一些系统错误时就可查看此目录下的日志文件

　　　　pg_stat_tmp             统计信息的存储目录

　　　　pg_tblsp                  存储了指向各个用户自建表空间实际目录的链接方式

　　　　pg_twophase            使用两阶段提交功能时分布式事务的存储目录 

　　　　pg_xlog:wal              日志的目录 

 

常见问题

 

　　1. ident....... 或者连接输入都正确但是连接不上，未开启访问数据库IP白名单

　　　　路径一般位置如下：vim /var/lib/pgsql/10/data/pg_hba.conf

　　　　如果找不到可以：$ find / -name pg_hba.conf

　　　　修改 or 增加　

　　　　host    all             all             127.0.0.1/32            trust