0
INSERT INTO O_USER (ID, NAME, LASTNAME, EMAIL, USERNAME, PASSWD, DATE_REG, DATE_UPD, DATE_DEL, IS_DELETE, TIPO, FTP) 
VALUES (%s,%s,%s,%s,%s,%s,DEFAULT,NULL,NULL,%s,%s,%s) 
ON CONFLICT ON CONSTRAINT ux DO NOTHING;

ux constraint:

CREATE UNIQUE INDEX ux ON public.o_user USING btree (email,username);

but in multiple insert have this error:

psycopg2.IntegrityError: duplicate key value violates unique constraint "o_user_username_key"
DETAIL:  Key (username)=(jferraro) already exists.

what is the problem?

update add table information:

CREATE TABLE O_USER (
    ID          INTEGER         PRIMARY KEY,
    NAME        VARCHAR(30)     NOT NULL,
    LASTNAME    VARCHAR(30)     NOT NULL,
    EMAIL       VARCHAR(50)     NOT NULL UNIQUE,
    USERNAME    VARCHAR(30)     NOT NULL UNIQUE,
    PASSWD      VARCHAR(30)     NOT NULL,
    DATE_REG    DATE            DEFAULT NOW(),
    DATE_UPD    DATE,
    DATE_DEL    DATE,
    IS_DELETE   NUMERIC         NOT NULL,
    TIPO        VARCHAR(10)     NOT NULL,
    FTP         VARCHAR(30)     NOT NULL,
    CONSTRAINT  DEL             CHECK       (IS_DELETE IN ('0','1')),
    CONSTRAINT  FK_FTP          FOREIGN KEY (FTP) REFERENCES FTP_USERS(FTP_USER),
    CONSTRAINT  UX              UNIQUE      (EMAIL,USERNAME),
    CONSTRAINT  TYP             CHECK       (TIPO IN ('ADMIN','STANDARD'))
);

update resolve the problem: the problem was that i declared two different mode for make unique email and username, for resolve this problem i delete unique near the collums and use only unique constraint "UX" for email and username. thanks all for the support.

  • 3
    You have another constraint named o_user_username_key which is violated. – a_horse_with_no_name Apr 15 at 13:56
  • Try \d o_* (or is it \dt o_*) from the psql client! +1 for a decent first question. p.s. welcome to the forum ;-) – Vérace Apr 15 at 14:45
  • i haven't other constraint called equal. The problems are 'UNIQUE' near collum and in constraint? i add the constraint after, because the 'ON CONFLICT' can't take multiple collums, and think make a constraint for make my life easier. thanks for rep :) – Foreign Apr 15 at 15:13
  • 1
    You have a primary key on id - that's also a unique constraint – a_horse_with_no_name Apr 15 at 15:15
1

This:

EMAIL       VARCHAR(50)     NOT NULL UNIQUE,
USERNAME    VARCHAR(30)     NOT NULL UNIQUE,

makes both EMAIL and USERNAME individually unique, in addition to your other constraint. If only a combination of EMAIL and USERNAME is to be unique, remove UNIQUE from the column definitions.

  • thanks, i supposed this, now try and work! – Foreign Apr 16 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.