​gp，pg常用数据类型，查询语句_gp数据类型

1、数值类型：

smallint     2 字节   小范围整数   -32768 到 +32767
integer      4 字节     常用的整数 -2147483648 到 +2147483647
bigint       8 字节   大范围整数   -9223372036854775808 到 +9223372036854775807
decimal     可变长 用户指定的精度，精确  小数点前 131072 位；小数点后 16383 位
numeric     可变长 用户指定的精度，精确  小数点前 131072 位；小数点后 16383 位
real         4 字节   可变精度，不精确    6 位十进制数字精度
double precision    8 字节    可变精度，不精确    15 位十进制数字精度
smallserial  2 字节   自增的小范围整数    1 到 32767
serial       4 字节   自增整数    1 到 2147483647
bigserial    8 字节   自增的大范围整数    1 到 9223372036854775807
1
2
3
4
5
6
7
8
9
10

2、字符类型：

1   character varying(n), varchar(n)   变长，有长度限制
2   character(n), char(n)  f定长,不足补空白
3   text  变长，无长度限制
1
2
3

3、货币类型

money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。
| 名字  | 存储容量 | 描述     | 范围                                         
| :---- | :------- | :------- | :-----------------------------------------
| money | 8 字节   | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
1
2
3
4
5

4、时间类型：

timestamp [ (p) ] [ without time zone ] 8 字节    日期和时间(无时区)  4713 BC 294276 AD   1 毫秒 / 14 位
timestamp [ (p) ] with time zone    8 字节    日期和时间，有时区   4713 BC 294276 AD   1 毫秒 / 14 位
date    4 字节    只用于日期   4713 BC 5874897 AD  1 天
time [ (p) ] [ without time zone ]  8 字节    只用于一日内时间    00:00:00    24:00:00    1 毫秒 / 14 位
time [ (p) ] with time zone 12 字节   只用于一日内时间，带时区    00:00:00+1459   24:00:00-1459   1 毫秒 / 14 位
interval [ fields ] [ (p) ] 12 字节   时间间隔    -178000000 年    178000000 年 1 毫秒 / 14 位
1
2
3
4
5
6

5、时间–日期相互转换

to_timestamp('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
to_char(time,'YYYY-MM-DD hh24:mi:ss') as time1,
to_char(time,'YYYY-MM-DD') as time2,
to_char(time,'YYYY-MM-DD hh:mi:ss') as time3
1
2
3
4

6、greenplum gp 常用命令

1、连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname

2、切换数据库,相当于MySQL的use dbname
\c dbname

3、列举数据库，相当于mysql的show databases
\l

4、列举表，相当于mysql的show tables
\dt

5、查看表结构，相当于desc tblname,show columns from tbname
\d tblname    \d+ tblname (显示注释)

6、查看索引
\di

7、查看函数
\df

8、创建数据库
create database [数据库名]; 

9、删除数据库：
drop database [数据库名];  

10、重命名一个表：
alter table [表名A] rename to [表名B]; 

11、删除一个表：
drop table [表名]; 

12、在已有的表里添加字段：
alter table [表名] add column [字段名] [类型]; 

13、删除表中的字段：
alter table [表名] drop column [字段名]; 

14、重命名一个字段：
alter table [表名] rename column [字段名A] to [字段名B]; 

15、给一个字段设置缺省值：
alter table [表名] alter column [字段名] set default [新的默认值];

16、去除缺省值：
alter table [表名] alter column [字段名] drop default; 

17、在表中插入数据：
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 

18、修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征]; 

19、删除表中某行数据：
delete from [表名] where [该行特征]; 
delete from [表名];--删空整个表 

20、创建表：
create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); 

21、显示 PostgreSQL 的使用和发行条款
\copyright    

22、显示或设定用户端字元编码
\encoding [字元编码名称]

23、SQL 命令语法上的说明，用 * 显示全部命令
\h [名称]     

24、提示用户设定内部变数
\prompt [文本] 名称

25、修改用户密码
\password [USERNAME]

26、退出 psql
 \q
 
27、修改字段类型
alter table ods_yqsj.ods_yqsj_xgyqsj  alter  COLUMN  nl  type varchar(100) ;

28、 创建schema
create schema + shName

​
------管理命令------
[创建schema] => CREATE SCHEMA myschema;
[创建并设置owner] =>  CREATE SCHEMA myschema AUTHORIZATION username;
[查看当前schema] => SELECT current_schema();
[查看search path] => SHOW search_path;
[修改search path] => ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;
[删除schema<必须是空schema,未包含任何对象>] => DROP SCHEMA myschema;
[删除schema及数据库中所有对象] => DROP SCHEMA myschema CASCADE;

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：
pg_dump drupal>/opt/Postgresql/backup/1.bak 

greenplum:空值转换函数
COALESCE 相当于Oracle中的nvl
例如：COALESCE(f1,0)

-- greenplum 查看每个schema的占用大小
select                 
    schemaname,
    round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024,2) as "GB"
from pg_tables
where schemaname in ('ods_test','ods_yqsj') -- 填入需要的名字
group by 1;
##参见该博客
https://blog.csdn.net/yueludanfeng/article/details/86487585

执行外部文件
\i 文件名.sql				导出结果集： \o 文件名.格式   --> 执行导出sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114

系统表及用途

表名字               用途
pg_aggregate         聚集函数
pg_am               索引访问方法
pg_amop             访问方法操作符
pg_amproc           访问方法支持过程
pg_attrdef          字段缺省值
pg_attribute        表的列(也称为"属性"或"字段")
pg_authid           认证标识符(角色)
pg_auth_members     认证标识符成员关系
pg_autovacuum       每个关系一个的自动清理配置参数
pg_cast             转换(数据类型转换)
pg_class            表、索引、序列、视图("关系")
pg_constraint       检查约束、唯一约束、主键约束、外键约束
pg_conversion       编码转换信息
pg_database         本集群内的数据库
pg_depend           数据库对象之间的依赖性
pg_description      数据库对象的描述或注释
pg_index            附加的索引信息
pg_inherits         表继承层次
pg_language         用于写函数的语言
pg_largeobject      大对象
pg_listener         异步通知
pg_namespace        模式
pg_opclass          索引访问方法操作符类
pg_operator         操作符
pg_pltemplate       过程语言使用的模板数据
pg_proc             函数和过程
pg_rewrite          查询重写规则
pg_shdepend         在共享对象上的依赖性
pg_shdescription    共享对象上的注释
pg_statistic        优化器统计
pg_tablespace       这个数据库集群里面的表空间
pg_trigger          触发器
pg_type             数据类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

查询所有表名称以及字段含义

SELECT
    b.schemaname,
    A.* 
FROM
    (SELECT C
        .relname,
        A.attnum,
        A.attname,
        --T.typname AS TYPE,
        concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS TYPE,
        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
        A.attnum > 0 
        AND A.attrelid = C.oid 
        AND A.atttypid = T.oid 
        AND A.attstattarget = - 1 
    ORDER BY
        A.attnum 
    )
    A LEFT JOIN pg_tables b ON b.tablename = A.relname 
WHERE
    b.tablename IS NOT NULL 
    --AND schemaname IN ( 'xxx', 'xxx', 'xxx'.... ) 
    AND schemaname = 'ods_test'
ORDER BY
    schemaname||relname ,
    attnum
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

postgres 修改最大连接数

 select count(1) from pg_stat_activity;

 show max_connections;

 show superuser_reserved_connections ;

select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

alter system set max_connections= 数量
1
2
3
4
5
6
7
8
9

查询锁表语句

select * from gp_toolkit.gp_locks_on_relation WHERE lorrelname like '%xxx%';
select pg_terminate_backend(id);
1
2

查询数据库字符集

select pg_encoding_to_char(encoding) from pg_database where datname = 'caiji_nianhua';
greenplum 查看每个schema的占用大小
select                 
    schemaname,
    round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024/1024,2) as "GB"
from pg_tables
where schemaname in ('xxx','xxx') -- 填入需要的名字
group by 1;
1
2
3
4
5
6
7
8

查询每个schema下表的数量

select schemaname,count(1) from pg_tables group by schemaname order by schemaname
1

​