查看当前文件夹在的文件使用ls -l，我们一般习惯了简写执行ll，但有时会报ll命令不存在，这是因为我们没配置自定义命令或自定义命令不生效导致的。
我们只需要在环境变量里添加一下内容即可：
alias ls='ls $LS_OPTIONS'
alias ll='ls $LS_OPTIONS -l'
alias l='ls $LS_OPTIONS -lA'
alias su='sudo su'
同样其他命令也可以这样做，但是具体加到那个环境变量的配置文件里，需要根据自己的操作系统实际多尝试几次才行。
如果是添加到系统级，则环境变量配置文件一般是：/etc/profile /etc/bash.bashrc /etc/bashrc；不同的操作系统可能需要放的文件不一样。
如果是用户级，则环境变量配置文件一般是：~/.bash_profile ~/.bashrc;当然不同的操作系统可能需要放的文件不一样。
---------------------------------------------------------------------------
--
-- syscat.sql-
-- sample queries to the system catalogs
--
--
-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
-- Portions Copyright (c) 1994, Regents of the University of California
--
-- src/tutorial/syscat.source
--
---------------------------------------------------------------------------
--
-- Sets the schema search path to pg_catalog first, so that we do not
-- need to qualify every system object
--
SET search_path TO pg_catalog;
-- The LIKE pattern language requires underscores to be escaped, so make
-- sure the backslashes are not misinterpreted.
SET standard_conforming_strings TO on;
--
-- lists the names of all database owners and the name of their database(s)
--
SELECT rolname, datname
FROM pg_roles, pg_database
WHERE pg_roles.oid = datdba
ORDER BY rolname, datname;
--
-- l
1、自定义postgresql安装目录
默认安装在/usr/local/pgsql下，configure 时加上 --prefix参数
2、查看\d开头命令执行的sql
1）源码src/bin/psql/describe.c下有执行sql的描述
2) psql登陆时加-E参数
3、部分可从数据库系统表中获取的信息
src/tutorial/syscat.source
Hibernate使用PostgreSQL序列生成主键。
第一步：数据库创建表、序列
CREATE TABLE users ( id int, firstname text ) ;
CREATE SEQUENCE SEQ_USER_ID;
第二步：实体类配置
@Entity
@Table(name = "users")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_gen")
@SequenceGenerator(name = "seq_gen", sequenceName = "seq_user_id", allocationSize = 1)
private Long id;
private String firstname;
}
第三步：测试
@Test
public void save(){
User u = new User();
u.setFirstname("name");
userRepository.save(u);
}
注意：使用@SequenceGenerator时，由于allocationSize值默认是50，如果序列INCREMENT属性值为1（不设置默认是1），则需要设置allocationSize属性为1；或者设置序列属性INCREMENT的值为50。
CREATE TABLE users ( id serial, firstname text, lastname text );
1、 Mybatis返回postgresql自增主键值
第一步：java对象
public class Users {
private int id;
private String firstname;
}
第二步：Mybatis xml
<insert id="insert_user_return_id" parameterType="Users" useGeneratedKeys="true" keyProperty="id">
INSERT INTO users (firstname, lastname) VALUES (#{firstname},#{lastname})
</insert>
第三步：测试
@Test
public void f2() {
Users u = new Users();
u.setFirstname("firstName");
u.setLastname("lastName");
userDao.insert_user_return_id(u);
System.out.println("key>>>>>>:" +u.getId());
}
注意：这里获取自增的主键值是通过传输的对象获取；建表时自增字段必须是第一个字段。
2、 使用returning返回数据
INSERT:
第一步：Mybatis接口中使用@Select注解
@Select("INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING * ")
Users insert_user_return_all();
第二步：测试
@Test
public void f1() {
Users user = userDao.insert_user_return_all();
System.out.println( "user id: "+user.getId() +", firstname: "+user.get
1、按月产生连续数据
highgo=# SELECT to_char( T, 'yyyy-mm' ) AS TIME FROM generate_series(to_timestamp('2021-01', 'yyyy-mm'), to_timestamp('2021-10', 'yyyy-mm'), '1 months') AS T;
time
---------
2020-01
2020-02
2020-03
2020-04
2、按天产生连续数据
highgo=# SELECT to_char( T, 'yyyy-mm-dd' ) AS TIME FROM generate_series(to_timestamp('2021-01-01', 'yyyy-mm-dd'), to_timestamp('2021-01-03', 'yyyy-mm-dd'), '1 days') AS T;
time
------------
2020-01-01
2020-01-02
2020-01-03
3、按小时产生连续数据
highgo=# SELECT to_char( t, 'yyyy-mm-dd hh24:mi:ss' ) AS TIME FROM generate_series ( to_timestamp( '2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), to_timestamp( '2021-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), '1 hours' ) AS t;
time
---------------------
2020-01-01 00:00:00
2020-01-01 01:00:00
. . .
2020-01-01 06:00:00
CREATE OR REPLACE FUNCTION db2_timestampdiff(i_type int, tp1 timestamp with time zone, tp2 timestamp with time zone)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
l_result number;
t_result number;
begin
l_result:=null;
--秒
if ( i_type = 2 ) then
select floor(extract(epoch from (tp1-tp2))) into t_result;
l_result := t_result;
end if;
--分钟
if (i_type = 4) then
select floor(extract(epoch from (tp1-tp2))/60) into t_result;
l_result := t_result;
end if;
--小时
if (i_type = 8) then
select floor(extract(epoch from (tp1-tp2))/60/60) into t_result;
l_result := t_result;
end if;
--天
if (i_type = 16) then
select floor(extract(day from (tp1-tp2))) into t_result;
l_result := t_result;
END IF;
--周
if (i_type = 32) then
select floor(extract(WEEKDAY from (tp1-tp2))) into t_resu
创建函数返回游标：
CREATE or REPLACE FUNCTION fun_cursor( in v_p1 int,in v_p2 int, refcursor, refcursor)
RETURNS SETOF refcursor AS $$
BEGIN
OPEN $3 FOR SELECT * FROM users where u_type = v_p1;
RETURN NEXT $3;
OPEN $4 FOR SELECT * FROM users where u_type = v_p2;
RETURN NEXT $4;
END;
$$ LANGUAGE plpgsql;
客户端调用：
BEGIN;
SELECT * FROM fun_cursor(1,2,'a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
MySQL函数功能：
按照关键字截取字符串，substring_index（被截取字段，关键字，关键字出现的次数）
PG建兼容函数：
create or replace function substring_index(text,text,int)
returns text as $$
declare
a text;
b text :='';
var int;
begin
for var in 1..$3 loop
a=split_part($1,$2,var);
b=b || $2 || a;
end loop;
return substring(b from 2 for length(b));
end
$$ language plpgsql
例子：
MySQL：
mysql> select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
+--------------------------------------------------------------+
| substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3) |
+--------------------------------------------------------------+
| fhafjkdalsfhiad,fjadsoj;f,ad |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
PG：
postgres=# select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
substring_index
------------------------------
fhafjkdalsfhiad,fjadsoj;f,ad
(1 行记录)
mysqlcompat-0.0.7是网上开源的postgresql兼容包；
以下是写sql兼容的方式，兼容mysql函数，此种方式效率会是瓶颈；
因此尽量改成postgresql原生的函数或者底层源码兼容；
1 -- BIT_XOR
-- Note: only works for integers and bigints
CREATE OR REPLACE FUNCTION _bit_xor(bigint, bigint)
RETURNS bigint AS $$
SELECT $1 # COALESCE($2, 0)
$$ IMMUTABLE LANGUAGE SQL;
CREATE AGGREGATE bit_xor (
BASETYPE = bigint,
SFUNC = _bit_xor,
STYPE = bigint,
INITCOND = 0
);
2 -- GROUP_CONCAT()
-- Note: only supports the comma separator
-- Note: For DISTINCT and ORDER BY a subquery is required
CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;
CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);