PostgreSQL

  1. 序列自增冲突解决参考链接

     SELECT relname FROM pg_class WHERE relkind = 'S' ORDER BY relname ASC;
     SELECT MAX(字段名) FROM 表名;
     ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT NULL;
     DROP SEQUENCE IF EXISTS 表名_字段名_seq;
     CREATE SEQUENCE 表名_字段名_seq START WITH 语句1的结果+1;
     ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT nextval('表名_字段名_seq');
    
  2. 批量去除指定字符

     # 去除指定列里的空格
     UPDATE user
     SET user_nick = REPLACE (user.user_nick, ' ', '')
     WHERE
     user_nick LIKE '% %'
    
  3. PostgreSQL连接字符串

     # 获取前100人序号,并拼接成'第16位'的格式
     SELECT
         '第' || user_order || '位' AS user_order
     FROM
         user
     WHERE
         user_order < 100
    
  4. 秒数转日期

     # 直接将时间戳转日期
     SELECT TO_CHAR(TO_TIMESTAMP(1525659845), 'YYYY-MM-DD HH24:MI:SS') AS "date";
     SELECT TO_CHAR(TO_TIMESTAMP('1525659845'), 'YYYY-MM-DD HH24:MI:SS') AS "date";
     # 将数据库字段转日期
     SELECT TO_CHAR(TO_TIMESTAMP(CAST(user.login_time AS INT)), 'YYYY-MM-DD HH24:MI:SS') AS "date";
    
  5. 秒数转日期格式符参考链接

  6. 触发器

     -- 假设有表
     -- user(id, user_name, insert_time)
     -- user_log(id, user_name, insert_time)
     -- 每当user表插入一条数据之后,触发向user_log表插入一条数据
    
     -- 创建一个自定义函数
     CREATE OR REPLACE FUNCTION add_user_log() RETURNS TRIGGER AS $TEST$
     BEGIN
     INSERT INTO user_log(
         id,
         user_name,
         insert_time
     ) VALUES (
         NEW.id,
         NEW.user_name,
         NEW.insert_time
     );
     RETURN NEW;
     END;
     $TEST$ LANGUAGE plpgsql;
    
     -- 创建一个触发器
     CREATE TRIGGER add_user_log AFTER INSERT ON "user" FOR EACH ROW
     EXECUTE PROCEDURE add_user_log();
    
     -- OLD用在删除和修改,NEW用在添加和修改
    
@耿志环 2012-∞ 冀ICP备17033181号, powered by Gitbook修订: 2019-07-08 16:49:59

results matching ""

    No results matching ""