kala-tamin Using psql to run an update


Using psql to run an update



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.

Thanks




What should I do to refresh data with concurrent sqlite 3 access

1:



Should i search content on database by id or name?
hmm, and if you:.
Why aren't OODBMS as widespread as RDBMS?
select idnazione, nextego  from partitaiva  where partitaiva='02469480244'  order by idnazione, nextego; 
you don't get any duplicate rows?.
Search-friendly way to store checkbox values in MySQL?
are you doing.
Using Application Id Generator vs. Database Id Generator
BEGIN; ... 


How can I include my database in the continuous integration process?
COMMIT;
when using pgadmin as well?.
How I can make Recycle Bin for Database ِApplication?


Storing Data in MS Access and Querying it in Excel

2:


Can you type the following in psql and add the output to your question?.
\d schema.table 


3:


Here is the output:.
              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 two columns idnazione and partitaiva are the keys (together with nextego but this is always equal to -1)..


4:


The real update statement is:.
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; 
The column "idnazione" is different in every row and more important the same statement in pgAdmin3 runs fine..



93 out of 100 based on 53 user ratings 453 reviews