入门知识点

发表于 2021-02-23 更新于 2023-01-25

postgresql 是另外一个与mysql类似的数据库系统，在sql上面，有很多跟mysql用法都类似，但是区别还是有的。下面专门记载一些有别与mysql的知识点，和一些基础的入门知识点。本文文章非常的长，基本上一些零散的知识点，都记录在本文中。本文可以看作一篇大杂汇，或者，简单的参考手册。

资源

按学习到一定程度后，重新对各资源排序。

《深入浅出PostgreSQL》
《GreenPlum:从大数据战略到实现》
《Greenplum企业应用实战》
官方的手册postgresql
greenplum官网，标准sql的格式

其次才是：

德哥的blog

https://billtian.github.io/digoal.blog/
https://www.php.cn/manual/view/20340.html
其他，搜索引擎。
一个私人博客，不确定是否有用 https://postgres.fun/
gp手册 https://gpdb.docs.pivotal.io/510/ref_guide/sql_commands/CREATE_TABLE.html
https://gpdb.docs.pivotal.io/6-2/main/index.html
https://www.lidihuo.com/postgresql/postgresql-index.html

对上面的资源，简单的总结一下，对于如何写计算sql，了解如何计算的，需要掌握的postgresql知识远超过GreenPlum。对于运维、导数据等一些特性，则需要看后者。官方的postgresql，非常的nice，有助于写复杂的sql，并用上特性。

至于后面德哥的博客，一开始比较看重，后来发现，其实并不重要，或者说从中得到的启发很少，还是从正规途径获取知识比较重要，德哥的博客，仅适合在拔高的时候，偶尔启发一下，因为感觉不够系统性。

其他资源

待看

http://blog.itpub.net/7551038/viewspace-614198/

零碎知识点

字段名用双引号来标识。如下：

sqlite3同样，而mysql是使用`符号。

SELECT "id","csv","exec_status" from td_csv_logs;

函数名，也可以加上双引号（也可省略）。

整体架构

https://www.cnblogs.com/nanshanjushi/p/11336687.html

创建表结构

CREATE TABLE td_csv_logs
(
    id bigserial,
    csv varchar(256),
    exec_status char(20),
    create_time timestamp without time zone,
    update_time timestamp without time zone
)
WITH (
    OIDS = FALSE,
    appendonly=true, COMPRESSTYPE=quicklz,orientation=column
)
DISTRIBUTED BY (id);

说明：

OIDS 是否生成抽象的id
appendonly 压缩表
COMPRESSTYPE压缩程度。1-9，一般为5。
orientation

查看数据库、表结构等

1、相当与mysql的show databases;

select datname from pg_database;

2、相当于mysql的show tables;

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

public 是默认的schema的名字

3、相当与mysql的describe table_name;

SELECT column_name FROM information_schema.columns WHERE table_name ='table_name';

‘table_name’是要查询的表的名字

psql中的快速查看：

\d    # 查看有哪些表
\d  table*   # 查看具体的表结构 * 表示模糊匹配，如果没有，则表示精确匹配

切换

psql用法

\c database

查看建表语句

https://www.cnblogs.com/nami/p/4112339.html

查看表占用空间

select pg_size_pretty(pg_total_relation_size('表'));


select pg_size_pretty(pg_relation_size('schema.tablename'));
select pg_size_pretty(pg_database_size('databasename));

查看分布

查看greenplum库各个节点数据的分布情况

select gp_segment_id,count(*) from table_name group by gp_segment_id;

https://www.cnblogs.com/gobird/p/3632547.html

分区倾斜

由于分区表的名称可以使用*来匹配，如下table_name*来匹配分区表名，查看每个分区大概占了多大空间。

\dt+ table_name*

多级分区

https://postgres.fun/20190716101600.html

分区表，能跟正常表一样，进行大多数的操作。我们可以清空某个分区表，能删除掉。

-- 建表时候的分区设置部分 
with (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)
Distributed by (分布键)
partition by range (分区时间字段) 
(
    PARTITION pn START ('2020-06-01 00:00:00'::timestamp without time zone) END ('2023-01-01 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval), 
    DEFAULT PARTITION pdefault
);

-- 查询某个分区的数据量
select count(1) from table_1_prt_pn_31;

-- 清空某个分区的数据，执行效率很快。
truncate tu_doc_info_1_prt_pn_31;
-- 另外删除

分区表的名称规则：原表名_1_prt_pn_序列号 ，其中pn是自己设置的。

思考题：

上面方式建立的分区，如何知道数据在哪个分区？如何知道要操作的时间段是哪个分区？笨的方法如下：

\dt+ i1_order*;   -- 查看每个分区的使用情况，找到一个貌似比较小的分区。
-- 查看该分区的数据
select * from i1_order_1_prt_pn_26 limit 1; 
--  清空分区。
truncate table i1_order_1_prt_pn_26;

上面的貌似能写成一个脚本。自动的来操作，或者写成一个函数？

更简单的方式如下：

select tableoid,* from i1_record limit 1;
select tableoid::regclass,* from i1_record limit 1;

另外还有的特殊字段：

ctid, tableoid,gp_segment_id

查看分区

select tablename,partitiontablename,partitionrank,partitionlevel,partitionrangestart,partitionrangeend from pg_partitions where tablename='表';

select * from pg_partitions where tablename='表';

解决如何查找数据属于哪个分区。

查看分区所在的文件分区

select pg_relation_filepath('分区表名');

 pg_relation_filepath 
----------------------
 base/16384/30155

base表示所使用的表空间是系统默认表空间（pg_default），16384是表所属数据库（postgres）的OID。可以使用“oid2name”命令查看数据库OID对应的数据库名和所属表空间名。

psql使用

https://www.cnblogs.com/yulinlewis/p/9404112.html

https://www.cnblogs.com/my-blogs-for-everone/p/10226473.html

连接数据库：

psql -h localhost -U gpadmin -d develop

参数说明

-h 连接的数据库ip，hostname的缩写
-p 指定端口。
-U 用户名，即账号的Username。
-d 数据库名称

执行命令：

如果只想执行一条命令而已，使用-c，示例见导入数据.

对于要执行的sql，要添加上分号，表示语句输入完成。然后psql才会输出内容。

控制台的其他命令：

\password: 设置密码
\q：退出查询 
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\d+
\dS+   tablename*; 查看所有分区
\dt+
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

\i   执行sql文件
\dt 显示表
\dtS 显示系统表
\dg or 	\du 显示角色

恢复数据 psql exampledb < exampledb.sql

执行批量的sql语句：

psql -h localhost -U gpadmin -d develop -f count.sql
#其中count.sql是多条要执行的命令。

增加快捷访问的别名

修改 vim ~/.bashrc 的内容。

alias pgsql='psql -h localhost -U gpadmin -d develop '

psql执行sql

根据psql本身提供的命令行参数，-c只执行1条命令,-f执行一个文件内容,如，取数任务中的sql。其实，除了以上两种方式，还隐藏第3种方式，即交互方式执行。示例如下：

LIMIT=2
psql -h localhost -U gpadmin -d develop << EOL
\timing on
select ship_id  from tb_scan limit $LIMIT;
-- 还可以有其他的操作等
select doc_sn form tu_doc_info limit $LIMIT;

EOL

LIMIT本身是shell中的变量，然后呢，在交互模式下，被heredoc解释为变量的真实值。在操作过程中，我们还可以取shell的调用参数作为值。

这太tm方便了，有木有！！！

但是，注意坑，即结尾的EOL，末尾不能多空格，等其他符号。

由此，我可以想象，mysql等交试工具也是可以的。

查询耗时

psql模式

\timing on

会显示每条sql语句的执行时间

不再使用时：
\timing off

方式2

explain select count(*) from 你的表;

方法3：

psql -d develop -f fetch.sql
# 或者
\i /yd/fetch.sql
# 而fetch.sql 里面增加下面一句。
\timing on;

输出内容

指定输出的位置。两个\o之间的内容会被输出到文件中。跟\i参数，正好是相对的。

develop=# \o /yd/hello.txt
develop=# select '20210901'::date;
Time: 1.332 ms
develop=# \o

导入数据

https://www.cnblogs.com/alianbog/p/5621660.html

连接的时候，需要指定要访问的数据库。

如果未使用-d来指定，那么默认数据库名与登录用户名同名。待考证。

psql -d  develop   -h  localhost   -U gpadmin  -p 5432  -c "\copy td_ci (ci) from /tmp/bm_order_data_uniq_trim_sort_jieba_sort.csv with csv header"

copy tb_scan from '/etl/dta/20200701/tb_scan_20200701100500.csv' with csv ;

使用gpadmin用户名
数据库 -d选择数据库
指定连接的host的ip -h
指定端口-p
指定执行的命令-c 。这个命令执行完就退出。如果需要进入交互式模式，则不应该使用该参数。

总结：

copy与\copy命令都能实现数据文件与表的数据传递，两者都在psql环境下执行。

主要不同在于数据文件的路径寻址：

1）copy 命令始终是到数据库服务端找文件；

2）\copy 命令可在客户端执行导入客户端的数据文件。

另外，常用的数据文件列之间默认是空格，可以用csv格式的传递，列之间以分号隔离。

完整例子

copy target_table_name (field_1, field_2, field_3)
from 'C:\sourceData.txt'
with (
    FORMAT csv,
    DELIMITER ',',
    escape '\',
    header true,
    quote '"',
    encoding 'UTF8'
)

貌似还能对null处理（待测试）

NULL as 'null string'"

出错问题：

psql -c "\copy lanjiejian from tmm_busi_intercept.csv with csv";


psql -c "\copy lanjiejian from tmm_busi_intercept.csv with csv";

导入时，进行简单的转换数据

sed '$d' $each| psql -d  develop -c "copy i1_import_raw from STDIN with csv";

上面的效果是，删除文件最后一行，然后导入数据。

导出数据

psql -h localhost -U gpadmin -d develop -c "copy td_count_tb_scan to '/tmp/count_tmp.csv'"
# 或者使用下面的形式
psql -h localhost -U gpadmin -d develop -c "\copy td_count_tb_scan to /tmp/count_tmp.csv"

上面第1行，如果不加入引号，会报错。

上面第2种查询，即使没有引号，也不会报错，能正常执行。

直接在psql命令行界面也能执行，但是，因为没有加上csv 的header，分隔符号等，导致样式有问题。采用下面优化一下。

copy mytable to '/tmp/mytable.csv' with csv header delimiter ','; 
# 当然，也能写查询的sql并导出。    sql需要用括号包裹
copy (select * from test_alldata_20210710 where ship_id = '3120' ) to '/gpdata/mytest.csv' with csv header delimiter ',';

导出表结构

https://www.yisu.com/zixun/221342.html

pg_dump -h localhost  -U gpadmin -p 5432  -s -t mytable develop   >mytable.sql

参数说明

-s 选项用来只导出表结构，而不会导出表中的数据
-t 选项用来指定要导出的数据库表
develop 数据库名称

另外一种，使用sql

select
(select relname||'--'||(select description from pg_description where objoid=oid and objsubid=0) as comment from pg_class where oid=a.attrelid) as table_name,
a.attname as column_name,
format_type(a.atttypid,a.atttypmod) as data_type,
(case when atttypmod-4>0 then atttypmod-4 else 0 end)data_length,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,
(case when a.attnotnull=true then 'Y' else 'N' end) as nullable,
col_description(a.attrelid,a.attnum) as comment
from pg_attribute a
where attstattarget=-1 and attrelid in (select oid from pg_class where relname in(select relname from pg_class where relkind ='r' and relname like 'td_%'))
order by table_name,a.attnum;

表名称条件 relname like 'td_%'

执行sql文件

增加上-f参数。

psql -d  develop   -h  localhost   -U gpadmin  -p 5432 -f mysql.sql

每个sql，要写;不能省略。

压缩表

http://blog.itpub.net/29162273/viewspace-2129556/

面向列存储的表

https://www.cnblogs.com/hider/p/9402862.html

日志文件

由于有日志文件的产生，导致master所在的磁盘非常的大。故，根据需求，直接删除即可。

cd /d/p1/master/gpseg-1/pg_log
rm -f  rm -f gpdb-2021-07-*
du -sh .  # 查看释放后的空间大小。

结果显示

https://blog.csdn.net/weixin_34038293/article/details/86263027

使用下面的命令进行开关。

\x

类似于mysql \G

窗口函数

https://www.jianshu.com/p/2172fd27f06a

利用窗口，取某种序列中，最值的那1条记录。

CREATE TABLE test_scan_qin WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS SELECT
    *
FROM
    (
        SELECT
            ship_id,
            ROW_NUMBER () OVER (
                PARTITION BY ship_id
                ORDER BY
                    ins_db_tm
            ) AS rn
        FROM
            tb_scan
        WHERE
            ins_db_tm >= '2021-7-19'
        AND ins_db_tm < '2021-7-20'
        AND scan_typ = '10'
        AND rmk_id = '1'
    ) AS T
WHERE
    T .rn = 1;  --  注意，ROW_NUMBER的值从1开始

聚合函数string_agg与array_agg

https://blog.csdn.net/u011944141/article/details/78902678

-- 不仅可以去重，还可以排序
select array_agg(distinct deptno order by deptno desc) from jinbo.employee;
-- array_agg 排序再array取值，例如查询每个部门第一个入职的人
select deptno, (array_agg(ename order by hiredate asc))[1] from jinbo.employee group by deptno;

小数除法

两个整数相除，结果还是整数，跟c语言的语法非常的相似。故，需要先转换其中的一个数。

参照：postgresql 除法保持小数位的方法

round(s.cnt/d.cnt::numeric,4)

具体如下：

\x
select 8/(100-3) as c1,
       round(8/(100-3) ,4) as c2,
       round(8/(100-3)::numeric ,4) as c3,
       8/(100-3)::numeric as c4
;

-[ RECORD 1 ]--------------
c1 | 0
c2 | 0.0000
c3 | 0.0825
c4 | 0.08247422680412371134

(1 row)

类型

数据库中基本的类型有数字、字符串、数组等。

序号	类型	备注
1	smallint	65536
2	int
3	bigint
4	char(10)
5	varchar(10)
6	text	最简单的方式，适合存数据用的时候，再转换类型。

类型转换

numeric

注意不要写拼写错了

参考资源：https://blog.csdn.net/qq_38486203/article/details/93030268

使用cast

cast ( 1 as numeric )

使用::

round(s.cnt/d.cnt::numeric,4)

双冒号有点局限性，

-- 肯定不能直接相除，整数除发，显然不行
count(ship_id)/count(doc_sn)
-- 方式1
-- count(ship_id)::numberic/count(doc_sn)
count(distinct tm)/(count(1))::numberic
-- 方式2
cast ( COUNT (ship_id) as numeric )  / cast (  COUNT (doc_sn) as numeric ),

说明，空字符串的时候，无法转换，则可以使用过滤。

空字符串错误提示：ERROR: invalid input syntax for type numeric: “”

where fields1::numeric != '' and fields1::numeric > 0.6

或者case when。

select case 
    when i1l_ftconfid = '' then -1
    when i1l_ftconfid::numeric >= 1.1 then 1.1
    when i1l_ftconfid::numeric >= 1 then 1
    else 
    -1
 end as possible

text

select 1::text;

varchar

select 1::varchar;

regclass

tableoid::regclass;

date

select '2021-09-01'::date;
select '20210901'::date;

interval

转换成时间间隔

--  -1 days
select '-1 days'::interval;
select now() - '30 days'::interval;

grouping set

https://blog.csdn.net/sunbocong/article/details/79097713

示例:

group by grouping sets((name),(class),())

相当于，有3个分组union在一起的。以name、class、空3种分组。

另外，cube/rollup是对上面的再次封装。

表空间

建表的时候，除了可以指定数据库，还可以指定空间。

https://blog.csdn.net/sunziyue/article/details/51170341

更改密码

psql

http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

\password postgres

登录的用户

set

数据库启动、关闭

一次升级过程中，差点忘了执行关闭gp服务的操作了。结果匆忙，就执行了gpstop，然后等待了半天，提示输入模式选择['(s)mart_mode', '(f)ast_mode', '(i)mmediate_mode']，输入f即停止了服务。（有连接，服务可能等待服务短掉，才会停）

先输入的gpstop 服务没有停掉，再另外一个窗口输入的gpstop -M fast，提示停止失败。所以，只能等gpstop的命令。

su - gpadmin
# 先调整时间，再启动
ntpdate 10.0.2.2
gpstart

# 停止服务 使用快速关闭模式   开关闭服务
gpstop -M fast

dblink/postgre_fdw

这两个工具都能连接其他的postgresql，如果需要连接mysql，则需要安装mysql_fdw扩展。

create extension dblink;
drop extension dblink;

https://blog.csdn.net/weixin_39540651/article/details/104005279

https://blog.csdn.net/weixin_39540651/article/details/101367310

gp_tooltik

一些工具函数

https://blog.csdn.net/xfg0218/article/details/100896956

查看用户会话和提交的查询等信息

select * from pg_stat_activity 该表能查看到当前数据库连接的IP 地址，用户名，提交的查询等。另外也可以在master 主机上查看进程，对每个客户端连接，master 都会创建一个进程。

ps -ef |grep -i postgres |grep -i con

评：常用的命令，查看数据库死在那个sql上了。

配置详解

暂时放一些配置的链接。

终端提示符设置

https://www.cnblogs.com/logsharing/p/8034322.html

设置

\set PROMPT1  '%/@%M:%>%R%#'

\set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%#%]'

如果想长期有效，则应该~/.pgsqlrc中设置。

提示可以包含终端控制字符，这些字符可以改变颜色，北京，或者提示文本的风格，或者改变终端窗口的标题。为了让 Readline 的行编辑特性正确运行，这些不可打印的控制字符必须设计成不可见的，方法是用 %[ 和 %] 包围它们。在提示符里可能出现这些东西的多个配对。

循环语句

简单的示例如下：以下代码可以直接在navicat等工具上执行。下面的代码，提供了一个非常主要的可能。比如，我们需要对一段时间内的每天，执行同样统计操作，则可以执行下面的操作。

DO $$
BEGIN
   FOR counter IN 1..5 LOOP
       RAISE NOTICE  'Counter:%',counter;
   END LOOP;
END;$$

涨见识了。

number of workfiles per query limit exceeded

报错如下：

psql:fetch.sql:545: ERROR:  number of workfiles per query limit exceeded  (seg15 10.181.86.12:55015 pid=9803)

找到文章：https://blog.csdn.net/u012948976/article/details/52695372

如果为SQL查询分配的内存不足，Greenplum数据库会创建溢出文件（也叫工作文件）。在默认情况下，一个SQL查询最多可以创建 100000 个溢出文件，这足以满足大多数查询。
该参数决定了一个查询最多可以创建多少个溢出文件。0 意味着没有限制。限制溢出文件数据可以防止失控查询破坏整个系统。
如果分配内存不足或者出现数据倾斜，则一个SQL查询可能产生大量溢出文件。如果超过溢出文件上限，Greenplum数据库报错。

疑惑的是：我并未在gp的配置中找到 gp_workfile_limit_files_per_query配置项。

查看数据库的锁情况

比如有些sql操作后，长时间没有结果，查看下锁情况。

select * from pg_stat_activity where state <> 'idle' and waiting_reason='lock'

表、库操作

表的名称，对于默认的表空间，可以省略。

创建表

create table as select

另外，参见查 create table as select

DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
--  具体的sql语句
DISTRIBUTED BY (ship_id) ;

万能的建表语句（分布+分区）

新建分区表，使用主键分布，时间分区。

CREATE TABLE tu_doc_info (
	doc_sn VARCHAR (20),
	dtime TIMESTAMP without time zone
)
with (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)
Distributed by (doc_sn)
partition by range (dtime) 
(
    PARTITION pn START ('2020-06-01 00:00:00'::timestamp without time zone) END ('2023-01-01 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval), 
    DEFAULT PARTITION pdefault
);
comment on table tu_doc_info is '拦截件表，author:myname,2021-07-30 17:29';
comment on column tu_doc_info.dtime is '系统时间';
comment on column tu_doc_info.doc_sn is '单据号';

备注：

Distributed by (column) ，其中column不用也不能加表的名称，如mytable.column，因为整个sql执行后，column的列是select出来的，应该是唯一、确定的。（变相的相当于加载了 select 字段上）

万能建表语句，除时间戳以外，都可以简单的用text来表示，这样，也不会存再导入失败的问题。除非明确知道数据的类型。

decimal可以单独用，不用指定后面两个参数。varchar也可以不用指定。但是，性能嘛……待研究。

Create table hlc_cmyk (color varchar, c decimal, m decimal, y decimal, k decimal);

create like

CREATE TABLE mytestss(
    LIKE tb_scan
) 
WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
)
DISTRIBUTED BY (ship_id)
partition by range (ins_db_tm) 
(
    PARTITION pn START ('2021-09-07 00:00:00'::timestamp without time zone) END ('2021-09-09 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval), 
    DEFAULT PARTITION pdefault
)
;

直接select into

bbbbb是一张不存在的表。

select * into bbbbb from gs where bm = '200095';

select * from bbbbb limit 1;

复制表结构

create table table_one (like table_two);

输出如下：

table doesn’t have ‘DISTRIBUTED BY’ clause, defaulting to distribution columns from LIKE table

采用一样的分布键。

采用create来创建表。

采用select来复制表

-- 故意不输出数据 用where 1!=1 貌似也是可以的
select * into i1_import_raw2 from  i1_import_raw limit 0; 
\d+ i1_import_raw2
-- 调整一下表结构
ALTER TABLE i1_import_raw2 DROP COLUMN ftconfid;

修改表结构

一般步骤

-- 更改结构
alter table ....

analyze i1_import_raw;
\d+ i1_import_raw;

增加字段

ALTER TABLE table_name ADD COLUMN kafka_time timestamp without time zone;

注意：不能指定插入的位置，只能插入在末尾。标准的sql就没有指定顺序的方式。变通：1、新建表，复制旧数据到新表，删除旧表，2、或者创建视图，来保证顺序一致。

修改字段类型

ALTER TABLE oos_order_data_02 ALTER COLUMN sender_name TYPE varchar(512);  

-- 修改字段，需要先类型转换。
ALTER TABLE mytable ALTER COLUMN vld_tm TYPE timestamp without time zone using vld_tm::timestamp without time zone;

vld_tm原始数据，是2021-08-03这种，即需要计算，才能转换,报错如：You might need to specify "USING vld_tm::timestamp without time zone"

修改表后，需要执行analyze mytable 命令，否则，联表查询的时候，貌似会有下面的错误。

For non-partitioned tables, run analyze (). For partitioned tables, run analyze rootpartition (). See log for columns missing statistics.

同理，将text类型，更改为int型，也需要更增加using 字段::integer转换类型。可能遇到空串无法转换问题。

using 实际上跟得是一个表达式。

ALTER TABLE "yunchou"."demo1" ALTER COLUMN "value" TYPE int4 using value::integer;
-- 出现空串无法转换时，则可以使用计算表达式来完成。
-- ERROR:  invalid input syntax for integer: ""  
ALTER TABLE "yunchou"."demo1" ALTER COLUMN "value" TYPE int4 using case value when '' then 0 else value::integer end;

删除字段

ALTER TABLE mytable DROP COLUMN myfield;

增加备注、修改备注

comment on column mytable.goods_sn is '物品编号';

有些情况下，是无法修改字段类型的，如：

ERROR: cannot alter type of a column used in a distribution policy

临时的解决方式是，重新建一张新表，然后将旧的数据迁移到新表中，然后，交换一下表名称，确认数据无误后，删除旧的表。

删除表

DROP TABLE 	mytable;

清空表

清空表比delete的方式快一点。但是pg不会清空原有的

TRUNCATE mytable;

修改表名称

alter table td_mail_02 rename to td_mail_new;

备份、恢复数据表

简单的方式，性能、效率估计不会太好。

-- 备份
pg_dump db -t table > table.out
pg_dump db > sqls.out
-- 恢复
psql -d gregp -f dball.out

增删改查

增

略

删

略

改

略

查

分组统计

使用第1个字段进行分组统计和排序，居然能简省写做1。好神奇。

类推：第2个字段用2 3 4

同事说记得sqlserver,oracle也可以省略

select hh,count(1) from td_count_tb_scan_m group by 1 order by 1;

查 create table as select

注意，select出来的表，应该有具体的名称，否则会有提示。?column?

DROP TABLE IF EXISTS test_scan_fen;
CREATE TABLE test_scan_fen WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS SELECT
    *
FROM
    (
        SELECT
            ship_id,
            scan_site,
            scan_emp,
            scan_tm,
            rmk_inf,
            rmk_id,
            CASE
        WHEN CHAR_LENGTH (rmk_inf) = 2
        OR rmk_inf SIMILAR TO '\d{1,}' THEN
            1
        ELSE
            0
        END AS is_mark_code,
        ROW_NUMBER () OVER (
            PARTITION BY ship_id
            ORDER BY
                scan_tm DESC
        ) AS rn
    FROM
        tb_scan
    WHERE
        ins_db_tm >= '2021-6-30'
    AND ins_db_tm < '2021-7-10'
    AND scan_typ = '24'
    ) AS T
WHERE
    T .rn = 1;

增加DISTRIBUTED

DROP TABLE IF EXISTS branch;
CREATE TABLE branch WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
)  AS 
SELECT
    bm,  -- 网点编码
    mc,  -- 网点名称
    szd, -- 区
    county.CityID as city_id, -- 市
    city.ProvinceID as province_id, -- 省
    sjdw,-- 上级公司id
    wdzzz_cw.zzz  -- 上级分拨id
FROM
    gs
LEFT JOIN county ON gs.szd = county.CountyID
LEFT JOIN city ON county.CityID = city.CityID
LEFT JOIN wdzzz_cw ON gs.bm = wdzzz_cw.wdbm
--# 果然，到官网瞬间能找到正确的用法。
--# https://gpdb.docs.pivotal.io/6-17/ref_guide/sql_commands/CREATE_TABLE_AS.html
DISTRIBUTED BY (bm) ;

居然，下面是错的，因为在select后，字段是确定的，故，直接使用最终的字段名即可。

DISTRIBUTED BY (t.bm) ;

复制表

另外，一个场景，详见数据迁移。

场景：原始的表，td_daike未加Distributed、partition命令。重新建一张新表，然后将数据导入到新表中。

CREATE TABLE td_daike_03 (
    id bigserial,
    process_time text,
    processor text,
)
with (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)

Distributed by (mail_no)
partition by range (insert_time) 
(
    PARTITION pn START ('2020-06-01 00:00:00'::timestamp without time zone) END ('2023-01-01 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval), 
    DEFAULT PARTITION pdefault
);

手动建表后，使用下面命令，拷贝到新表中。1亿条数据，greenplum 1、2分钟就搞定。

-- 比较适合，td_daike_03表已经存在，td_daike_03新表，有分区，td_daike旧表，无分区
insert into td_daike_03 select * from td_daike;

-- 如果两张表的列数不一致，那么可以填加上具体的列
-- 比较适合，td_mail_03    INSERT 0 109585400   14:28  14:48(看时，已经结束）
insert into td_mail_03 select ship_id,send_id,recv_id,kafka_time,insert_time from td_mail_02;

insert into jd_order_waybill_info_03(kafka_time,insert_time) select   cast(kafka_time as timestamp),insert_time from jd_order_waybill_info_02 limit 2;

-- 部分字段测试
insert into i1_order2 (字段列表) select 字段列表  from i1_order;
-- 部分字段，经测试发现，，insert部分也能省略
insert into i1_order2 select 字段列表  from i1_order;

下面命令，会自动建一个同样结构的表。如果手动建了td_daike_03表反而会报错。适合相同的表结构。

--  直接会新建一个表td_daike_03，然后迁移数据到其内。
select * into td_daike_03 from td_daike;

交换表名称

alter table td_mail_02 rename to td_mail_old; 
alter table td_mail_03 rename to td_mail_02;

删除旧表

drop td_mail_old;

数据表复制过程中，遇到了如下错误：

ERROR: column “kafka_time” is of type timestamp without time zone but expression is of type character varying。

首先，尝试着更改类型：

ALTER TABLE jd_order_waybill_info_02 ALTER COLUMN kafka_time TYPE timestamp without time zone;

使用如下方式，解决插入：

-- 先执行分析表，否则，会有提示。（因为之前执行了alter table kafka .....）
analyze jd_order_waybill_info_02; 

-- 真正的插入语句，省略了部分
insert into jd_order_waybill_info_03 select cast(kafka_time as timestamp),insert_time from jd_order_waybill_info_02 limit 2;

增加索引

CREATE INDEX send_id_idx
    ON td_mail_02 (send_id);

查询相关

创建视图

create view scc_day_percent as

示例：

create view scc_day_percent as 
SELECT
    s.site,
    s.cnt AS scan_cnt,
    d.cnt AS doc_cnt,
    CASE
WHEN s.cnt IS NOT NULL
AND d.cnt IS NOT NULL THEN
    round(d.cnt/s.cnt::numeric,4)
ELSE
    0
END AS percent
FROM
    scc_day_scan_site AS s
LEFT JOIN scc_day_doc_site AS d ON s.site = d.site
AND s.riqi = d.riqi;

删除视图：

-- 语法
DROP VIEW view_name
-- 示例
drop view scc_day_percent;

distinct

直接

SELECT DISTINCT ship_id, gs from test ; -- 这个依然是3条记录
SELECT DISTINCT ship_id from test ; -- 这个导是能选择出唯一的 1.2

INSERT INTO . ("ship_id", "gs") VALUES ('1', 'shanghai');
INSERT INTO . ("ship_id", "gs") VALUES ('1', 'shanghai2');
INSERT INTO . ("ship_id", "gs") VALUES ('2', 'shanghai');

row_number去重

貌似下面的去重是一种通用的方式。先通过窗口查询，然后再从结果集中取结果。

SELECT
    *
FROM
    (
        SELECT
            *, ROW_NUMBER () OVER (
                PARTITION BY branch_code,
                salesman_id
            ORDER BY
                update_time DESC
            ) AS rn
        FROM
            ydwr_base_salesman_sorting
    ) t
WHERE
    rn = 1;

distinct on

distinct on需要排序，而且排序的字段，必须出现在on后的括号内，貌似不太好用。待深究。

select distinct on (ship_id,batch) *  from test_alldata_20210710 order by  batch desc

case when

虽然自己琢磨case when没有参照这篇文章，但是觉得还不错。

计算

case 
    when  ldly = 'CAINIAO' then 1
    when  ldly = 'YDEC' then 2
    when  ldly = 'JD' then 3
    else  4
end as ldsj_val,

有的时候，统计使用

SELECT 
    SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
    SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
    SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
    SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
    THTF_STUDENTS

对比下面两个例子，实际上是表达同一个意思。

SELECT
	CASE ship_id    -- 注意，这个是针对同个字段
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        ELSE 'other'
	END AS nu,
    ship_id,
    gs
FROM test;

SELECT
	CASE     -- 这个，没有字段
        WHEN ship_id = 1 THEN 'one'
        WHEN ship_id = 2 THEN 'two'
        ELSE 'other'
    END AS nu,
    ship_id,
    gs
FROM test;

排序

排序的时候，跟在order by 。

ORDER BY 居然不能使用select后面的计算字段，故突发奇想，直接放到order by后面算了，（类似于having 后面直接跟表达式一样。）

后发现文章：https://www.cnblogs.com/178mz/p/6428958.html

select
*
from 
(
    select
        *,
        ROW_NUMBER () OVER (
            PARTITION BY ship_id
            ORDER BY
                case 
                when  ldly = 'CAINIAO' then 1
                when  ldly = 'YDEC' then 2
                when  ldly = 'JD' then 3
                when  ldly = 'PDD' then 4
                when  ldly = 'BM' then 5
                when  ldly = 'YJ' then 6
                when  ldly = 'PDDGJ' then 7
                when  ldly = '其他' then 8
                when  ldly = 'OOS_NON_ELE' then 9
                when  ldly = 'OOS' then 10
                when  ldly = 'PC_REAL_NAME_NON_LEL' then 11
                when  ldly = 'PC_REAL_NAME' then 12
                when  ldly = 'COD' then 13
                else  14
            end ASC,ldsj desc    -- 优先从ldly（按规则顺序取），若相等，则判断ldsj（取最晚）
        ) AS rn
    FROM
        test_alldata_20210710
) t
where rn = 1

验证：

下面表示要将ship_id为1的放在最后。确实达到效果了。

SELECT * 
FROM test 
order by case ship_id when 1 then 2 else 1 end asc;

函数使用

array_agg

array_agg(distinct 想要合并的数据)：将想要的数据变成数组。

array_to_string

array_to_string(array_agg(distinct p.c_name), ' , ')

string_to_array

string_to_array(c.c_groups, ‘,’)：将字符串按照“，”分隔成数组。

string_to_array

转换成数组。

any

left join on p.id = any (string_to_array(c.persons, ',')::int[])

id = any(List)：id的值存在于List中，注意List要和id为同种类型。

with recursive

with recursive ytt(f1,f2) as (
values (0,' '::text)
union all
select f1+1,split_part('love,you,hate,number',',',f1+1) from ytt where f1 < 20
)

字符串函数

大小写转换

SELECT ship_id,lower(gs),upper(gs) from test;

regexp_split_to_array

-- 没有转义，注意下面的结果返回为数组的表示方法。5个元素
SELECT regexp_split_to_array('a|b|c','|')
{a,|,b,|,c}

-- 转义一次，才是正确的结果
SELECT regexp_split_to_array('a|b|c','\|')
{a,b,c}


-- 取第3个元素，注意函数返回的结果，增加了括号，增加优先级
SELECT (regexp_split_to_array('a|b|c','\|'))[3]

-- 之前以为可能需要增加别名才能用，实际上增加括号优先级即可。
-- SELECT a[3] from (  SELECT regexp_split_to_array('a|b|c', '|') as a ) t;

-- 拿真实的表测试。
select (regexp_split_to_array(rule,'\|'))[2] as rule2 from addr_real limit 10;

regexp_split_to_table

https://www.cnblogs.com/shenjiangwei/p/14239675.html

切割单词到sql的1条记录。统计地址中出现最多的字。

select regexp_split_to_table('您好世界','')

DROP TABLE IF EXISTS test_split_world;
CREATE TABLE test_split_world WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
select regexp_split_to_table(recv_addr,'') as word from tu_doc_info where dtime >='2021-08-01' and dtime < '2021-08-02'
DISTRIBUTED BY (word) ;

DROP TABLE IF EXISTS test_split_world_count;
CREATE TABLE test_split_world_count WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
select word,count(1) as cnt from test_split_world group by word 
DISTRIBUTED BY (word) ;

出现最多的词呢？

pg_trgm

安装方式：https://blog.csdn.net/qq_35260875/article/details/106148664

正则 SIMILAR TO

之前用SIMILAR TO总是，判断失败，后来发现，写的正则必须要匹配整个单词（不能只写匹配字段的部分内容的正则）（也可以理解它默认省略了^正则$)。类似于like。

‘[S]%’ 匹配以S开头的单词，’[S]’匹配字母s

-- 示例1  注意，%不能少，否则匹配的是单个的内容
SELECT * from test where gs SIMILAR TO  '[S]%';


--示例2，'\d{1,}'   只能匹配全数数字的这种情况，如果是数字开头，则 \d%
CASE WHEN CHAR_LENGTH (rmk_inf) = 2 OR rmk_inf SIMILAR TO '\d{1,}' THEN
    rmk_inf
ELSE
    ''
END AS mark_code,

substring

貌似序号从1开始。

substring(upper(gs) from 1 for 2 )

SELECT ship_id,lower(gs),substring(upper(gs) from 1 for 2 ) = 'SH' from test;
-- 更简单的方法
left(upper(gs),1)='S'

case when substring(upper(udf_4) from 1 for 2 ) = 'MD' then
                udf_4
            else 
                null
            end as last_signed_code,

left/right

取左边几个字符

left(str text, n int)

-- 中文也适用
select left('您好，世界',2);
 left 
------
 您好

length

length(str)

regexp_matches

regexp_match

select regexp_matches('foobarbequebaz','a')

split_part

split_part(string text, delimiter text2, field int)

text=“name.cn” split_part(text,'.',1) 结果： name
text=“name.cn” split_part(text,'.',2) 结果： cn
text=“name.cn.com” split_part(text,'.',3) 结果： com

时间函数

时间to_char

说明to_char的第一个参数，是数据库中的列，时间戳格式的。而字符格式的时间如，2021-08-02 22:20，类型不匹配，无法直接使用。第二个参数，是时间格式，具体见pg的手册说明。

-- 测试用法
SELECT to_char(dtime,'YYYY-MM-DD') from tu_doc_info limit 10;

to_char(dtime,'YYYY-MM-DD HH24:MI:SS')    -- HH12按12小时制

-- 示例
select count(1),count(doc_src) from tu_doc_info where  dtime>= '2021-07-01' and dtime < '2021-07-11' group by to_char(dtime,'YYYY-MM-DD');

to_timestamp

一般入库的时候使用2021-08-23 15:46这种格式，设置好类型，不需要额外的转换，能自动转换成时间戳。但是呢，并非总有这么简单的需求。如下

欲将I120210823112000，转换为时间戳格式，2021-08-23 11:20:00+08

参见文章,官方也有比较好的说明，但是篇幅太多，不太好找。

-- 先处理字符串，然后再调用处理成时间戳
select  substring('I120210823112000' from 3)
select to_timestamp(substring('I120210823112000' from 3),'yyyyMMddhh24miss')


-- 不能直接转换
-- select  substring('I120210823112000' from 3)::TIMESTAMP ;
-- 必须要指定格式 select to_timestamp('20210823112000')

聚合

max

分组后，select的字段，都需要进行聚合运算，而没有加入分组的字段，可以用max/min等计算一次，伪分组

min

同max，分组或者不分组都可以。

string_agg

select  ship_id,string_agg(ship_type,'|') as ship_type 
from (select ship_id,ship_type from marked_ship group by ship_id,ship_type ) as f group by  ship_id



SELECT string_agg(a, ',' ORDER BY a) FROM table;

数组

array的常见用法

定义数组

ARRAY[1,2]

SELECT max(id) from unnest(ARRAY[1,2]) as id;

-- 字符串比较顺序，输出的是 1111  简单理解，按字符排序
SELECT max(id) from unnest(ARRAY['1111','11102']) as id;

unnest

首先，自定义函数

create or replace function pyinc(a integer) returns int[] as $$
return [a,a+1,a+2]
$$ language plpythonu;

调用函数，返回的是数据。

select pyinc(10);
   pyinc    
------------
 {10,11,12}

然后，将数组，转换为table的一列。

select unnest( pyinc(10));           
 unnest 
--------
     10
     11
     12
(3 rows)

generate_series

产生一个序列。

select generate_series(1,10);

下面是一个复杂的例子。

drop table if exists tmp_array;

create table tmp_array (
  id   int8,
  name varchar(100)[]
);


insert into tmp_array
(id,name)
select mod(id,9999) as m_id,
	   array_agg(id::varchar) as m_id_agg
  from generate_series(1,1000000) as id
 group by mod(id,9999)
;

create index idx_tmp_array_x1 on tmp_array using gin(name);

explain ANALYZE select *
from tmp_array ta
where 1=1
and name @> array['113'::varchar];

其他函数

coalesce

返回它的第一个非空参数的值。具有短路效果，即，参数按顺序，找到不为空的值后，后续的表达式将不会被计算。

能在部分场景代替case when is not null then else end语法。

select coalesce(null/22,null,1);  --返回1
select coalesce(null,22,2/0,null,1);  --返回22，后面参数  22/0不会报错
select coalesce(null,null);    -- 返回空  null

GREATEST

GREATEST(n1,n2,n3,……….) 获取最大值。多列比较，而不是在聚合比较。

SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);

LEAST

对上面的反函数。

LEAST(N1,N2,N3,N4,……) 获取最小值。

函数udf

文字分割测试

CREATE OR REPLACE FUNCTION  strip_text(str text,len integer) RETURNS TEXT[] AS $$
DECLARE
rest text[];
n  INTEGER;
i INTEGER;
BEGIN
  n = char_length(str);
  if n <=len THEN
    rest[1] = str;
	  return rest;
  END IF;
  n = n - (len-1);
	for i in 1..n LOOP
		rest[i] = substring(str from i for len);
	end LOOP;
	RETURN rest ;
END; 
$$ LANGUAGE plpgsql;

-- 测试
-- 返回数组
SELECT strip_text('1234567',2);
-- 返回多列
SELECT unnest(strip_text('1234567',2));

DROP TABLE IF EXISTS test_split_world2;
CREATE TABLE test_split_world2 WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
-- 由于自定义函数没有对null的处理，所以，要过滤
select unnest(strip_text(recv_addr,2)) as word from tu_doc_info where dtime >='2021-08-01' and dtime < '2021-08-02' and recv_addr is not null
DISTRIBUTED BY (word);

DROP TABLE IF EXISTS test_split_world_count2;
CREATE TABLE test_split_world_count2 WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
select word,count(1) as cnt from test_split_world2 group by word 
DISTRIBUTED BY (word);

执行时间，由此可见，自定义函数的效率也是杠杠的。

SELECT 1745202170
Time: 169993.833 ms

DROP TABLE IF EXISTS test_split_world3;
CREATE TABLE test_split_world3 WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
-- 由于自定义函数没有对null的处理，所以，要过滤
select unnest(strip_text(recv_addr,3)) as word from tu_doc_info where dtime >='2021-08-01' and dtime < '2021-08-02' and recv_addr is not null
DISTRIBUTED BY (word);

DROP TABLE IF EXISTS test_split_world_count3;
CREATE TABLE test_split_world_count3 WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
) AS 
select word,count(1) as cnt from test_split_world3 group by word 
DISTRIBUTED BY (word);

distribute + 分区

本意：提升查询速度、解决/dev/mapper/vg1-lv1占用50%以上，而其他分区站15%。但是，删除上，并未解决。

数据经过distribute、分区，查询总数的性能反而会下降。以下*_old，表示旧表，普通建表语句，没有分区，distribute也没有写。而02后缀的表是distribute、分区。

当然，查询1天之类的，由于能进行剪切，速度当然会很快。

develop=# select count(*) from p_order_waybill_info_old;                                                                                                                                                   
   count    
------------
 1622729642
(1 row)

Time: 59826.072 ms
develop=# select count(*) from p_order_waybill_info_02;                                                                                                                                                    
   count    
------------
 1622729642
(1 row)

Time: 172275.076 ms
develop=# select count(*) from t_order_waybill_info_old;                                                                                                                                                   
   count    
------------
 2726513394
(1 row)

Time: 202499.226 ms
develop=# select count(*) from t_order_waybill_info_02;                                                                                                                                                    
   count    
------------
 2726513394
(1 row)

Time: 296512.553 ms

其他参见，查 create table as select

个人感受：

为啥gp能那么快，首先，distribute，会将相同的运单号，放到同一台worker机器上，那么，这台woker机器，做join运算的时候，就可以在内部完成计算。比如日5000万的量，那么每天机器，实际上才上百万的计算，这样就非常的方便。分区，能保证，分隔在哪天的量上。

镜像

pg

docker run --rm --name postgre10 -it -v $PWD:/mywork -w /mywork postgres:10.1 bash

案例

导入+转换

导入

单纯的使用shell，将数据加载到gp中，然后再从临时表中，插入到目标表中。

#!/bin/bash

import_data(){
    table=$1
    echo '清空临时表i1_import_raw'
    psql -d  develop   -h  localhost   -U gpadmin  -p 5432  -c "truncate table i1_import_raw";
    csv_path="/i1/$table"
    for day in `cat day_list.txt`;do
        for each in `ls $csv_path/$day/*00.csv`;do
            echo $each 
            #each=/addr7/full_piece/real/20210421/addr7_20210421044500.csv
            psql -d  develop   -h  localhost   -U gpadmin  -p 5432  -c "\copy i1_import_raw from $each with csv";
            if [ $? -ne 0 ];then
                echo "error: import $each"
            fi
        done
    done;
    echo "导入到正式表 i1_$table 中"
    psql -d  develop   -h  localhost   -U gpadmin  -p 5432  -c "insert into i1_$table select *,to_timestamp(substring(batch from 3),'yyyyMMddhh24miss') as batch_time from i1_import_raw;"
}

import_data 'order'
import_data 'record'

区分以下sql用法

-- 不会创建表
insert into newtable select * from oldtable
-- 会创建旧表
select * into newtable from oldtable;

转换

其他

导入的时候，比如多个csv文件，1、要不要合并成一个csv文件，并导入。（不要，因为：首先合并csv需要一定的io时间。也许合并成一个sql文件，调用一次psql -f import_csvs.sql，但是担心因为某个sql错误，导致后续的sql异常退出，无法执行？？待检验）

问题

csv导入转型问题

CREATE TABLE lanjiejian (
    ship_id varchar(20), 
    create_time timestamp without time zone,
    from_table smallint
)

而导入的csv，ship_id，是纯数字，没有加引号。