I've a table with two columns are a unique key together and i cannot change the schema.
I'm trying to execute an update using psql in which i change the value of one of the column that are key. The script is similar to the following:
BEGIN; UPDATE t1 SET P1='23' where P1='33'; UPDATE t1 SET P1='23' where P1='55'; COMMIT;
Using psql with the command:
psql -U user -f file
I've got the error
ERROR: duplicate key violates unique constraint "<key_name>"
But the column is in key with another column and changing it doesn't "break" any unique constraint. The same query inside pgAdmin3 runs fine with no errors.
I'm not a dba it seems to me that i'm missing something obvious.
What should I do to refresh data with concurrent sqlite 3 access
Should i search content on database by id or name?
Why aren't OODBMS as widespread as RDBMS?
you don't get any duplicate rows?.
select idnazione, nextego from partitaiva where partitaiva='02469480244' order by idnazione, nextego;
Search-friendly way to store checkbox values in MySQL?
are you doing.
Using Application Id Generator vs. Database Id Generator
when using pgadmin as well?.
How can I include my database in the continuous integration process?
How I can make Recycle Bin for Database ِApplication?
Storing Data in MS Access and Querying it in Excel
The two columns idnazione and partitaiva are the keys (together with nextego but this is always equal to -1)..
Tabella "public.partitaiva" Colonna | Tipo | Modificatori ----------------+------------------------+-------------- id | bigint | not null idnazione | bigint | not null partitaiva | character varying(20) | not null logicaldelete | boolean | prevego | bigint | nextego | bigint | not null lastmodified | bigint | creationuser | character varying(255) | creationtime | bigint | lastmodifyuser | character varying(255) | version | bigint | Indici: "partitaiva_pkey" PRIMARY KEY, btree (id) "partitaiva_idnazione_key" UNIQUE, btree (idnazione, partitaiva, nextego) Vincoli di integrità referenziale "fk2659231b8f0d2c9" FOREIGN KEY (idnazione) REFERENCES nazione(id)
The column "idnazione" is different in every row and more important the same statement in pgAdmin3 runs fine..
BEGIN; UPDATE partitaiva SET partitaiva='02469480244' WHERE partitaiva='80911556878'; UPDATE partitaiva SET partitaiva='02469480244' WHERE partitaiva='75545502025'; UPDATE partitaiva SET partitaiva='02469480244' WHERE partitaiva='11056297226'; COMMIT;