postgresSQL建表规范

必须要有注释

必须要有四个字段

id,create_time,create_user,update_time,update_user

尽量自动赋值

id,create_time,update_time

更新时间赋值较为复杂

先创建函数，该函数可以复用给所有表，但是每个表需要有update_time字段

CREATE OR REPLACE FUNCTION "update_timestamp"()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

再将函数与表绑定，修改表名称执行即可

CREATE TRIGGER "更新time" BEFORE UPDATE ON "t_demo"
    FOR EACH ROW
EXECUTE PROCEDURE "update_timestamp"();

以下是完整的SQL 

drop table if exists t_demo;
create table t_demo(
    id bigserial primary key,
    name varchar(20) not null,
    create_time timestamp not null default current_timestamp,
    create_user varchar(20) not null,
    update_time timestamp not null default current_timestamp,
    update_user varchar(20) not null
);
comment on table t_demo is 'demo表';
comment on column t_demo.name is '姓名';

-- 创建函数
CREATE OR REPLACE FUNCTION "update_timestamp"()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

--
CREATE TRIGGER "updated_time" BEFORE UPDATE ON "t_demo"
    FOR EACH ROW
EXECUTE PROCEDURE "update_timestamp"();

insert into t_demo(name, create_user, update_user)
values('dzh', 'lucy', 'lily');

update t_demo set update_user = 'jack' where id = 1;

select * from t_demo;