Skip site navigation (1) Skip section navigation (2)

Re: Disruptive corruption of data during an update

From: Binand Sethumadhavan <binand(at)gmx(dot)net>
To: Ennio-Sr <nasr(dot)laili(at)tin(dot)it>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Disruptive corruption of data during an update
Date: 2012-03-29 11:33:03
Message-ID: CAFBJCCa9e0XJhcJG3u7WK5thS4XMejTSBEq52CCgAuSOLcJ2uQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 29 March 2012 16:47, Ennio-Sr <nasr(dot)laili(at)tin(dot)it> wrote:
> --------
> mydb=> update bibl set argomento='curatori', autore='DELLA VOLPE Galvano \
>       (con due capitoli aggiunti e note di)  where n_prog=3285;
> mydb'> ';
>
> UPDATE 12845
> mydb=>
> --------
>
> Having an adequate back-up I think I'll be able to recover the data, but
> can someone explain what the hell happened?

Because of the single quote being at the wrong place, your query was
processed as though not having a where clause at all, and the ENTIRE
table got updated with the text:

DELLA VOLPE Galvano \
       (con due capitoli aggiunti e note di)  where n_prog=3285;

in the autore field (and similarly in the argomento field).

What I typically do is to attach "where 1 = 0" to inhibit a query from
making any changes, if I find an error in a multiline query and do not
want to Control-C out of psql.

Binand

In response to

Responses

pgsql-novice by date

Next:From: Ennio-SrDate: 2012-03-29 11:49:01
Subject: Disruptive corruption of data during an update
Previous:From: Ennio-SrDate: 2012-03-29 11:17:29
Subject: Disruptive corruption of data during an update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group