Using psql to run an update

Question

Hi there,

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

Answer 1

Can you type the following in psql and add the output to your question?

\d schema.table

Answer 2

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).

Answer 3

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.

Answer 4

hmm, and if you:

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

you don't get any duplicate rows?

are you doing

BEGIN;
...
COMMIT;

when using pgadmin as well?

Answer 5

I found the error, i wasn't doing BEGIN .. COMMIT in pgAdmin.

Thanks !