postgresql

//创建用户并设置密码
CREATE USER animal WITH PASSWORD 'animal_dev';

//创建schema并分配给某用户
CREATE SCHEMA animal AUTHORIZATION animal;
GRANT ALL ON SCHEMA animal TO animal;

//之后新建的表都需要分配所有者
ALTER table fud_test owner to animal;

postgresql insert 出现duplicate key value violates unique constraint错误 原因：从别处导入的表其id已经占据了sequence正常增长时所到达的值

//查询当前最大的id号
SELECT max(id) from fud_fund_product;
查询下一个序列号
SELECT nextval('fud_fund_product_id_seq');
将下一个序列号设置为最大id+1
SELECT setval('fud_fund_product_id_seq',(SELECT max(id) from fud_fund_product)+1);

Long seqNum = extSequencesDAO.nextValue("seq_user_no");
String userNo = String.format("%08d", seqNum);

CREATE TABLE your_schema.myTableName (
  id serial primary key,
  from_user_no CHARACTER VARYING(32), -- 订单起始方
  order_no CHARACTER VARYING(32), -- 申请单号
  to_user_no CHARACTER VARYING(32), -- 订单目标方
  agency_pay CHARACTER VARYING(2), -- 代付标记: 0 正常、 1 代付
  CONSTRAINT "UQE_ON_ORDER_NO" UNIQUE (order_no)
);
COMMENT ON COLUMN myTableName.from_user_no IS '订单起始方';
COMMENT ON COLUMN myTableName.order_no IS '申请单号';
COMMENT ON COLUMN myTableName.to_user_no IS '订单目标方';
COMMENT ON COLUMN myTableName.agency_pay IS '代付标记: 0 正常、 1 代付';

ALTER TABLE myTableName OWNER TO your_schema;

CREATE INDEX idx_order_no on your_schema.myTableName(order_no);
CREATE INDEX idx_to_user_no on your_schema.myTableName(to_user_no);

CREATE SEQUENCE order_no_seq
  INCREMENT 1
  START 1
  MINVALUE 1
  MAXVALUE 1000000000000
  CACHE 1;
ALTER SEQUENCE order_no_seq OWNER TO your_schema;
COMMENT ON SEQUENCE order_no_seq is '订单号序列';