2021-09-26 14:09:57    19    0    0
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。​
2021-09-26 14:09:57    16    0    0

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

2021-09-26 13:47:42    12    0    0
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
     
2021-09-26 13:09:22    10    0    0

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

2021-09-26 11:15:16    14    0    0

创建函数返回游标:

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;

2021-09-26 10:22:58    9    0    0

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 行记录)

 

2021-09-24 16:30:20    7    0    0

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

);

 

2021-09-24 16:25:41    6    0    0

mysqlcompat-0.0.7是网上开源的postgresql兼容包;

以下是写sql兼容的方式,兼容mysql函数,此种方式效率会是瓶颈;

因此尽量改成postgresql原生的函数或者底层源码兼容;

 

1 -- BIT_COUNT()

CREATE OR REPLACE FUNCTION bit_count(bigint)

RETURNS integer AS $$

  SELECT pg_catalog.length(pg_catalog.replace(pg_catalog.ltrim(pg_catalog.textin(pg_catalog.bit_out($1::bit(64))), '0'), '0', ''));

$$ IMMUTABLE STRICT LANGUAGE SQL;

2021-09-24 16:23:20    5    0    0

mysqlcompat-0.0.7是网上开源的postgresql兼容包;

以下是写sql兼容的方式,兼容mysql函数,此种方式效率会是瓶颈;

因此尽量改成postgresql原生的函数或者底层源码兼容;

 

1 -- TIME TO INTEGER

CREATE OR REPLACE FUNCTION _time_to_integer(time with time zone)

RETURNS integer AS $$

  SELECT

    EXTRACT(HOUR FROM $1)::integer * 10000

    + EXTRACT(MINUTE FROM $1)::integer * 100

    + EXTRACT(SECONDS FROM $1)::integer

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (time with time zone AS integer)

  WITH FUNCTION _time_to_integer (time with time zone)

  AS IMPLICIT;

CREATE OR REPLACE FUNCTION _time_to_integer(time without time zone)

RETURNS integer AS $$

  SELECT

    EXTRACT(HOUR FROM $1)::integer * 10000

    + EXTRACT(MINUTE FROM $1)::integer * 100

    + EXTRACT(SECONDS FROM $1)::integer

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (time without time zone AS integer)

  WITH FUNCTION _time_to_integer (time without time zone)

  AS IMPLICIT;

 

2 -- DATE TO INTEGER

-- Note: requires explicit casts in some cases

CREATE OR REPLACE FUNCTION _date_to_integer(date)

RETURNS integer AS $$

  SELE

2021-09-24 16:21:52    6    0    0

mysqlcompat-0.0.7是网上开源的postgresql兼容包;

以下是写sql兼容的方式,兼容mysql函数,此种方式效率会是瓶颈;

因此尽量改成postgresql原生的函数或者底层源码兼容;

 

1 -- IF

-- Warning: still requires casts in some instances

CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)

RETURNS anyelement AS '

  SELECT CASE WHEN $1 THEN $2 ELSE $3 END

' IMMUTABLE LANGUAGE SQL;

 

2 -- IFNULL

-- Warning: still requires casts in some instances

CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)

RETURNS anyelement AS '

  SELECT COALESCE($1, $2)

' IMMUTABLE LANGUAGE SQL;

1/3