pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBug in psql - Postgresql 7.3.1?
6+ messages / 5 participants
[nested] [flat]
* Bug in psql - Postgresql 7.3.1?
@ 2003-10-03 13:18 John B. Scalia <jscalia@cityblueprinting.com>
0 siblings, 4 replies; 6+ messages in thread
From: John B. Scalia @ 2003-10-03 13:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug in psql - Postgresql 7.3.1?
@ 2003-10-03 19:37 Wei Weng <wweng@kencast.com>
parent: John B. Scalia <jscalia@cityblueprinting.com>
3 siblings, 0 replies; 6+ messages in thread
From: Wei Weng @ 2003-10-03 19:37 UTC (permalink / raw)
To: John B. Scalia <jscalia@cityblueprinting.com>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug in psql - Postgresql 7.3.1?
@ 2003-10-03 19:45 Josh Berkus <josh@agliodbs.com>
parent: John B. Scalia <jscalia@cityblueprinting.com>
3 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-10-03 19:45 UTC (permalink / raw)
To: John B. Scalia <jscalia@cityblueprinting.com>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug in psql - Postgresql 7.3.1?
@ 2003-10-03 19:47 Bruno Wolff III <bruno@wolff.to>
parent: John B. Scalia <jscalia@cityblueprinting.com>
3 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2003-10-03 19:47 UTC (permalink / raw)
To: John B. Scalia <jscalia@cityblueprinting.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug in psql - Postgresql 7.3.1?
@ 2003-10-03 20:16 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2003-10-03 20:16 UTC (permalink / raw)
To: John B. Scalia <jscalia@cityblueprinting.com>; +Cc: pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug in psql - Postgresql 7.3.1?
@ 2003-10-03 20:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: John B. Scalia <jscalia@cityblueprinting.com>
3 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2003-10-03 20:27 UTC (permalink / raw)
To: John B. Scalia <jscalia@cityblueprinting.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-10-03 20:27 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-03 13:18 Bug in psql - Postgresql 7.3.1? John B. Scalia <jscalia@cityblueprinting.com>
2003-10-03 19:37 ` Wei Weng <wweng@kencast.com>
2003-10-03 19:45 ` Josh Berkus <josh@agliodbs.com>
2003-10-03 19:47 ` Bruno Wolff III <bruno@wolff.to>
2003-10-03 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-03 20:16 Re: Bug in psql - Postgresql 7.3.1? Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox