本文档详述了Greenplum数据库中的数值、字符、货币等数据类型，并提供了时间类型及其相互转换的方法。此外，还介绍了greenplum gp常用命令，包括连接数据库、创建表、修改数据等操作，以及如何管理和查询schema。
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
2、字符类型：
1 character varying(n), varchar(n) 变长，有长度限制
2 character(n), char(n) f定长,不足补空白
3 text 变长，无长度限制
3、货币类型
money 类型存储带有固定小数精度的货币金额。
numeric、int 和 bigint 类型的值可以转换为 money，不建议使用浮点数来处理处理货币类型，因为存在舍入错误的可能性。
| 名字 | 存储容量 | 描述 | 范围
| :---- | :------- | :------- | :-----------------------------------------
| money | 8 字节 | 货币金额 | -92233720368547758.08 到 +92233720368547758.07 |
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 位
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
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
系统表及用途
表名字 用途
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 数据类型
查询所有表名称以及字段含义
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
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= 数量
查询锁表语句
select * from gp_toolkit.gp_locks_on_relation WHERE lorrelname like '%xxx%';
select pg_terminate_backend(id);
查询数据库字符集
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;
查询每个schema下表的数量
select schemaname,count(1) from pg_tables group by schemaname order by schemaname
扫一扫
2118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
