Thread: Bug in psql - Postgresql 7.3.1?
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">All,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I’m not certain if what I’m trying to do is legal, but if I execute a statement like:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">UPDATE my_table SET field1=’new_value’ AND SET field2=’different_value’ WHERE my_table_id = ‘key’;</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">in psql, it reports that it has successfully updated one record. However, the record does not appear tobe updated when I subsequently issue a SELECT for that record. I’ll buy the fact that I may be using illegal syntax – I’mnot SQL expert, but the update says it worked when in fact it did not. Should this have worked or should it have reportedan error?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally seen during a php4 script, but hasbeen duplicated on the psql command line.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">TIA,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">John</span></font></div>
On Fri, Oct 03, 2003 at 09:18:44 -0400, "John B. Scalia" <jscalia@cityblueprinting.com> wrote: > All, > > > > I'm not certain if what I'm trying to do is legal, but if I execute a > statement like: > > > > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; It should be: UPDATE my_table SET field1='new_value', field2='different_value' WHERE my_table_id = 'key'; > in psql, it reports that it has successfully updated one record. I am not sure why you aren't getting a syntax error. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'll buy the fact that I may be using > illegal syntax - I'm not SQL expert, but the update says it worked when > in fact it did not. Should this have worked or should it have reported > an error?
Shouldn't that be "UPDATE my_table SET field1 = 'new_value', field2 = 'different_value' WHERE my_table_id = 'key';"? Wei On Fri, 3 Oct 2003, John B. Scalia wrote: > All, > > > > I'm not certain if what I'm trying to do is legal, but if I execute a > statement like: > > > > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; > > > > in psql, it reports that it has successfully updated one record. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'll buy the fact that I may be using > illegal syntax - I'm not SQL expert, but the update says it worked when > in fact it did not. Should this have worked or should it have reported > an error? > > > > For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally > seen during a php4 script, but has been duplicated on the psql command > line. > > > > TIA, > > John > >
John, > > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; Well, your SQL is bad: UPDATE my_table SET field1='new_value, field2='different_value' WHERE my_table_id = 'key'; > in psql, it reports that it has successfully updated one record. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'll buy the fact that I may be using > illegal syntax - I'm not SQL expert, but the update says it worked when > in fact it did not. Should this have worked or should it have reported > an error? It's an error if it actually occurred that way. Can you cut-and-paste your actual PSQL session? > For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally > seen during a php4 script, but has been duplicated on the psql command > line. BTW, there are known bugs in 7.3.1; you should upgrade to 7.3.4. -- -Josh BerkusAglio Database SolutionsSan Francisco
John, > Yeah, I figured out my SQL was bad and had switched to the comma > separated version, instead. In my mind, the first form should have > caused an error. I've attached a cut-and-pasted session from psql where > I used this syntax on a test table. While edited for brevity and to > obscure passwords, this is how the output appeared. Here's your problem: accounting=# update all_user set usr_current = True AND usr_location = 1002; UPDATE 3 PostgreSQL interpreted the expression "True AND usr_location = 1002" as a single, unitary, boolean expression. AND is the boolean AND operator. Since none of the users on your list had "usr_location = 1002", you got: user_current = (True AND (usr_location = 1002)) user_current = (True AND False) user_current = False Since all 3 rows already had false, they did not appear to get updated, but in fact they were. Time to look up your order of operations! -- -Josh BerkusAglio Database SolutionsSan Francisco
"John B. Scalia" <jscalia@cityblueprinting.com> writes:
> UPDATE my_table SET field1='new_value' AND SET field2='different_value'
> WHERE my_table_id = 'key';
The other responses have focused on your obvious syntax error, but I'm
assuming you didn't actually cut-and-paste that from your psql session.
> in psql, it reports that it has successfully updated one record.
> However, the record does not appear to be updated when I subsequently
> issue a SELECT for that record.
I'm wondering if you are checking from another transaction that doesn't
think the first one is committed yet. Are you using BEGIN/END or
autocommit-off mode?
regards, tom lane