Clever Geek Handbook

Using psql to trigger an update

I have a table with two columns - this is a unique key and I cannot change the schema.

I am trying to perform an update using psql in which I change the value of one of the key columns. 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 have an error

ERROR: duplicate key violates unique constraint "<key_name>"

But the column is in the key with another column, and changing it does not "break" any unique constraint. The same request inside pgAdmin3 works fine, no errors .

I am not dBA, it seems to me that I have missed something obvious.

thank

0


source to share


4 answers


hmm, and if you:

select idnazione, nextego 
from partitaiva 
where partitaiva='02469480244' 
order by idnazione, nextego;

do you have duplicate lines?



you do

BEGIN;
...
COMMIT;

when using pgadmin?

0


source


Can you enter the following into psql and add the output to your question?



\d schema.table
0


source


Here's the result:

              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 keys (along with nextego, but this is always -1).

0


source


Real update operator:

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 "idnazione" column is different on each line, and more important is the same statement in pgAdmin3.

0


source






More articles:


All Articles