使用psql运行更新

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:

我正在尝试使用psql执行更新,其中我更改了其中一个键的值。该脚本类似于以下内容:

BEGIN;
UPDATE t1 SET P1='23' where P1='33';
UPDATE t1 SET P1='23' where P1='55';
COMMIT;

Using psql with the command:

使用psql命令:

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.

但是该列与另一列关键,并且更改它不会“破坏”任何唯一约束。 pgAdmin3中的相同查询运行正常,没有错误。

I'm not a dba it seems to me that i'm missing something obvious.

在我看来,我不是一个dba,我错过了一些明显的东西。

Thanks

4 个解决方案

#1

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?

什么时候使用pgadmin?

#2

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

你能在psql中输入以下内容并将输出添加到你的问题中吗?

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

两列idnazione和partitaiva是键(与nextego一起但总是等于-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.

列“idnazione”在每一行都不同,更重要的是pgAdmin3中的相同语句运行正常。

#1