不同于Mysql，数据库Postgresql,在使用上语法还是有区别，以下记录常用的语法命令。

数据与字段类型

数据类型

分类	说明
基本数据类型	数据库内置数据类型，例如integer、char、varchar等
复合数据类型	需要用户自定义，所谓复合就是把多个基本类型融合在一起使用，复合类型的字段实际上就是一个特殊的数组
域	一种特殊的基本数据类型，由基本数据类型加上约束条件构成
伪类型	“伪”说明它不是真正的数据类型，这些数据类型不能作为表的列类型，只能作为函数的参数或返回值的数据类型

字段类型定义

PostgreSQL	Java SE 8
numeric	BigDecimal
DATE	LocalDate
TIME [ WITHOUT TIMEZONE ]	LocalTime
TIMESTAMP [ WITHOUT TIMEZONE ]	LocalDateTime
TIMESTAMP WITH TIMEZONE	OffsetDateTime

自定义数据类型

-- 看当前数据库里所有的自定义类型
\dT

数据库

查看所有数据库

SELECT datname FROM pg_database;

用户

查看所有用户

select * from pg_user;

查看用户所拥有权限

例如查看test用户的权限

select * from information_schema.table_privileges where grantee='test';

表

查看表结构

例如查询表tb_test的表结构

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 = 'tb_test'
       and a.attnum > 0
       and a.attrelid = c.oid
       and a.atttypid = t.oid
 ORDER BY a.attnum;

查看建表语句

\d+ table_name

授权表

 GRANT ALL PRIVILEGES ON TABLE 表名 TO 用户名

复制表结构

CREATE TABLE new_table_name ( like old_table_name including all)

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

序列

查询所有序列

SELECT "c"."relname" FROM "pg_class" "c" WHERE "c"."relkind" = 'S';

或

\ds

创建序列

例如创建序列 tb_test_seq

create sequence tb_test_seq increment by 1 minvalue 1 no maxvalue start with 1;

查看序列权限

\dp 序列名称

授权序列

 grant all on sequence 表名 to 用户名

函数

授权函数

grant execute on function 完整函数名 to 用户名

视图

删除视图

drop view 视图名

授权视图

grant select on 视图名 to 用户名

索引

重建索引

-- 重建表上的索引：
REINDEX TABLE 表名;

-- 重建单个索引：
REINDEX INDEX 索引名称;

-- 重建全部索引
reindex database 数据库名称;

查询

数值字段搜索

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

ILIKE

类似于like模糊查询。只不过是没有区分大小写搜索。

WITH OIDS=FALSE

常见问题处理

表没权限

-- 查看一个表的授权情况
select FROM information_schema.role_table_grants  WHERE table_name ='表名';

-- 赋予所有表的所有权限给用户
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO 用户名; 

-- 赋予用户，指定表的所有权限
GRANT ALL PRIVILEGES ON 表名 TO 用户名;

导出数据库的所有表结构信息

select 
table_catalog 数据库名称,
table_name 表名,
column_name 字段名,
ordinal_position ordinal_position,
column_default 字段默认值,
is_nullable 是否为空,
data_type 字段类型,
character_maximum_length 以字符为单位的最大长度,
character_octet_length 以字节为单位的最大长度,
numeric_precision 数字数据的精度,
numeric_precision_radix 数字数据的精度基数,
numeric_scale 数字数据的小数位数


from 
information_schema.columns 
where table_schema = 'public'
-- 业务表都是以tb_开头，所以加了这个条件
and table_name like 'tb_%'
order by table_name;

PostgreSQL 数据库常用语法总结