pgsql的一些语法

计算两个时间相差的天数#

SELECT date_part('day',cast(now() as TIMESTAMP)-cast(create_time as TIMESTAMP)) FROM t_module_xxx;

create_time 是 timestamp 类型。可以通过date_part计算两个时间相差几天，几分钟，几秒钟等。

具体可以参考这篇文章。

可重复执行修改表结构语法#

添加约束

alter table public.t_module_xxx drop constraint if EXISTS unique_xxxx;
alter table public.t_module_xxx add constraint unique_xxxx unique(col1,col2);

建表语句#

-- postgresql建议使用小写
drop table if exists public.t_module_xxx;
drop sequence if exists seq_t_module_xxx;
-- 创建序列
create sequence seq_t_module_xxx increment 1 minvalue 1 maxvalue 9223372036854775807 start 1 cache 1 cycle;
-- 创建表
create table public.t_module_xxx
(
  order_id            integer default nextval('seq_t_pcar_order') not null,
  user_id             integer not null,
  loan_amount         numeric(12, 2),
  loan_apply_no       varchar(255),
  loan_apply_status   varchar(2),
  loan_apply_time     timestamp without time zone,
  loan_no             varchar(255),
  loan_success_time   timestamp without time zone,
  loan_status         varchar(2),
  -- 金额类型
  down_payment_amount numeric(12, 2),
  create_time         timestamp without time zone default now(),
  update_time         timestamp without time zone
);
-- 创建注释
comment on table public.t_module_xxx is '订单表';
comment on column public.t_module_xxx.order_id is '主键';
--设置主键
alter table t_module_xxx add constraint pk_t_pcar_order primary key (order_id);

-- 授权语句
grant select, insert, update, delete on public.t_module_xxx to xxxxxx;
grant select on public.t_module_xxx to xxxxxx;