sql server 换成 postgresql 遇到的相关问题

1. postgresql 没有TOP 替换 limit
2. postgresql  没有len 替换char_length
3. postgresql 没有charindex替换strpos( t2.vehi_number, '-' || lpad(cast(t1.tax_vehi_number as VARCHAR), 4, '0') )

4. postgresql 没有 with (updlock) 替换 LOCK 

5. postgresql 生成UUID  

   md5(cast (ceil(random()*(10000-1)+1) as VARCHAR)) as id  

   https://www.itranslater.com/qa/details/2112753497207735296
6. org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone >= character varying  解决方案：Timestamp alarmStartTime = Timestamp.valueOf("2011-05-09 11:49:45");
7. postgresql 递归：https://www.jb51.net/article/161399.htm
8. ERROR: relation "hibernate_sequence" does not exist 异常 ---创建主键 
   create sequence HIBERNATE_SEQUENCE
   minvalue 100000
   maxvalue 9999999999999999
   start with 100060
   increment by 1
   cache 20;
9.  RIGHT(REPLICATE('0',4)+CAST(T1.TAX_VEHI_NUMBER AS VARCHAR(4)),4)  替换 lpad( CAST ( t1.tax_vehi_number AS VARCHAR ), 4, '0' ) 
10. ISNULL() 替换 coalesce（）
11. 字符串拼接 || T.NAME_PLATE||'('||T.TERMINAL_SNO||')(已删除)'  

12.  GETDATE()  -(2008-12-29 16:25:46.635)替换方案 now() -(2014-12-24 09:28:31.545145+08);

13. ### 1. 设置主键自增长

    ```plsql
    CREATE TABLE "public"."auth_log" (
      "auth_log_id" SERIAL8 PRIMARY KEY,
      "auth_ts" int8 NOT NULL,
      "user_name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
      "ip" varchar(16) COLLATE "pg_catalog"."default" NOT NULL,
      "port" int4 NOT NULL,
      "result" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
      "create_ts_sys" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "update_ts_sys" timestamp(6)
    );
    ```

    > 将 "auth_log_id" 定义为 SERIAL8 PRIMARY KEY， 用于主键自增长
    >
    > 将 "create_ts_sys" 列的默认值设置为CURRENT_TIMESTAMP，当新行insert时，自动填充创建时间
    >
    > 创建自增序列 CREATE SEQUENCE 表名xxxx_id_seq
    >
    > 修改表中列的默认值为自增序列 alter table myTableName alter column myColumnName set default nextval('xxxx_id_seq')
    >
    > 修改自增id的起始值为100，alter sequence xxxx_id_seq restart with 100
    >
    > 查询当前自增id的值，  select nextval ('xxxx_id_seq' )

    #### 判断序列是否存在，不存在则创建

    ```plsql
    DO
    $do$
    BEGIN
    if (SELECT count(*) FROM pg_class where relname = 'xxxx_id_seq') = 0
    then
    CREATE SEQUENCE xxxx_id_seq START 1;
    end if;
    END;
    $do$
    ```

    #### 查询所有的序列

    ```plsql
    select * from pg_class where relkind = 'S';
    ```

    ### 2. 时间戳自动更新

    #### 2.1 创建函数，用于更新表中的时间字段

    ```plsql
    CREATE OR REPLACE FUNCTION update_update_ts_sys_column()
    BEGIN
       NEW.update_ts_sys = now(); 
       RETURN NEW;
    END;
    $$ language 'plpgsql';
    ```

    > 当表数据发生变化时， New.**update_ts_sys**是表中想要更新时间戳的列名称

    #### 2.2 创建触发器，当表中行数据有变化时，执行更新时间函数

    ```plsql
    CREATE TRIGGER update_auth_log_changetimestamp BEFORE UPDATE
        ON auth_log FOR EACH ROW EXECUTE PROCEDURE 
        update_update_ts_sys_column();
    ```

14. 

15. 丢失时分秒的时候可能时数据库字段类型错误需要使用TIMESTAMP

16. select last_time+7 from table1  需要修改成 select LAST_TIME+ cast(7||' days' as interval) from table1

17.    递归查询  WITH RECURSIVE  r as (
            SELECT  T.* FROM ORGANIZATION T 
        LEFT JOIN SYS_USER SU ON T.ID = SU.ORG_ID 
        WHERE  SU.ID = 'f05c4bd7-782c-49d5-863f-0aef078c4156' AND T.STATUS<>9  
            UNION ALL  
        SELECT T1.*  FROM ORGANIZATION T1,r 
        WHERE  T1.PARENT_ID = r.ID AND T1.STATUS<> 9 
        
        )SELECT  * FROM r