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+1SELECT 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 '订单号序列';