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用在添加和修改
    

results matching ""

    No results matching ""