PostgreSQL: PostgreSQL数据库使用踩坑记录

0、数据库依赖可以这样写：

<dependency>       
     <groupId>org.postgresql</groupId>     
     <artifactId>postgresql</artifactId>      
     <version>9.4.1212</version>    
</dependency>

1、数据库的连接信息可以这样写：

String driver = "org.postgresql.Driver";
String url = "jdbc:postgresql://<ip地址>:<端口>/<数据库名>";
String username = <用户名>;
String password = <密码>;

2、如果PostgreSQL数据库下面有默认的模式，那就要在url后面加一句：

currentSchema=<当前使用的模式名>

3、URL的其他参数：

useSSL=false
serverTimezone=Asia/Shanghai

4、在PostgreSQL里面传入时间戳，除非用statement的字符串拼接来传时间戳数字，否则会出各种问题，参数不能为character varying、text等。所以如果要传入时间戳来比较时间的话，最好入参字符串时间，比如用以下的操作：

SELECT TO_TIMESTAMP('2018-10-01', 'YYYY-MM-DD')

5、PostgreSQL没有MySQL的group_concat函数，也没有Oracle的wm_concat函数，但是可以通过这样的方式来实现聚合功能(array_to_string(array_agg(聚合列))，然后后面可以自定义分隔符，下例就是以英文逗号作为分隔符)：

SELECT
    group_by_column,
    array_to_string ( array_agg ( aggregate_column ), ',' ) AS aggregate_column_alias
FROM
    data_table
GROUP BY
    group_by_column

6、PostgreSQL里面的时间格式，是这样的（本例是将时间戳按格式转化为字符串）：

TO_CHAR(a.create_date, 'YYYY-MM-DD HH24:MI:SS') AS create_date

7、PostgreSQL里面的一些转换：

--字符转数字
SELECT cast('123' as integer)
--数字转字符
SELECT to_char(123, '999')
--如果不知道数字位数的，后面的9模式可以多写点，但是就会导致有空格，需要trim
SELECT trim(to_char(123, '999999'))

8、PostgreSQL中类似Oracle的NVL函数的函数，就是如果为空，就指定一个默认值的一个函数：coalesce

SELECT coalesce(table.data, 0) AS data FROM table

9、PostgreSQL中如果想通过入参时间戳(long型数据)来插入或修改时间，则需要除以1000，再入参to_timestamp()函数。比如要入参当前时间，则：

//Java代码：先获取当前时间的时间戳
long current_time = System.currentTimeMillis();

//转化为字符串
String current_time_str = String.valueOf(current_time);

//得到除以1000后的字符串：得到转化后的13位字符，除以1000，四舍五入
String longStr = new BigDecimal(current_time_str).divide(new BigDecimal("1000"), 0, BigDecimal.ROUND_HALF_UP).toString();

//得到结果
long current_time_parse = Long.parseLong(longStr);

//入参到PostgreSQL数据库
SELECT to_timestamp(current_time_parse)

10、如果想给PostgreSQL的表加自增主键，则需要先创建一个序列，序列名可以是表名加上"_seq"，其执行的SQL如下：

CREATE SEQUENCE "空间名"."表名_seq"  
 INCREMENT 1  
 MINVALUE 1  
 MAXVALUE 9223372036854775807  
 START 1  
 CACHE 1;

然后对应数据库的id主键自增字段，默认值需要改成如下：

nextval('表名_seq'::regclass)

11、将long型的字符串或数字转化为事件字符串的方法（假设入参时间戳为：1566975652828）

//long的字符串转时间字符串
TO_CHAR(TO_TIMESTAMP(CAST ('1566975652828' AS int8) / 1000),'YYYY-MM-DD HH24:MI:SS')

//long的数字转时间字符串
TO_CHAR(TO_TIMESTAMP(1566975652828 / 1000),'YYYY-MM-DD HH24:MI:SS')

//二者都可以得到结果：2019-08-28 15:00:52

12、将时间字符串，转化为时间戳或者long值（假设入参时间字符串为：‘2019-08-28 15:00:52’）

//时间字符串转时间戳
TO_TIMESTAMP('2019-08-28 15:00:52', 'YYYY-MM-DD HH24:MI:SS')

13、在PostgreSQL里面是可以生成连续的日期字符串的，示例如下：

//生成2019年5月29日到2019年6月3日的，格式为 "yyyy-MM-dd" 的连续日期字符串
SELECT
	to_char(t.day::date, 'yyyy-MM-dd') AS date 
FROM 
	generate_series(
		timestamp '2019-05-29', 
		timestamp '2019-06-03', 
		interval '1 day'
	) AS t(day)

执行上面的SQL，PostgreSQL就会生成以下的结果集：
date
2019-05-29
2019-05-30
2019-05-31
2019-06-01
2019-06-02
2019-06-03