Thread: What is wrong with this PostgreSQL UPDATE statement??

Sorry for the fairly long post.

I'm having a big problem trying to update one table from another in
PostgreSQL 8.3.1.

I have a lookup table called termgroup:

# select * from termgroup;
termgroupname | mindays | maxdays
---------------+---------+---------
1-30 days     |       1 |      30
31-59 days    |      31 |      59
60-89 days    |      60 |      89
90-119 days   |      90 |     119
120-179 days  |     120 |     179
180-364 days  |     180 |     364
1-2 years     |     365 |     729
2-3 years     |     730 |    1094
3-4 years     |    1095 |    1459
4-5 years     |    1460 |    1824
5+ years      |    1825 |  999999
(11 rows)

And also a data table with data that needs to be catagorized using the
above lookup table:

# select * from certgroups;
days | number | termgroupname
------+--------+---------------58 |     66 |303 |     11 |732 |   1056 |
1096 |     66 |25 |    123 |
(5 rows)

As you can see from the detailed session below, the update statement
that works perfectly in MS-SQL 2005 fails miserably in PG.

ANY SUGGESTIONS WOULD BE GREATLY APPRECIATED!!

Thanks,
S.

----------- START of SQL session:

create table termgroup (
termgroupname varchar(20) not null,
mindays int not null,
maxdays int not null,
CONSTRAINT "PKtermgroup_termgroupname" PRIMARY KEY (termgroupname)
);
--
insert into termgroup (termgroupname,mindays,maxdays) values ('1-30 days',1,30);
insert into termgroup (termgroupname,mindays,maxdays) values ('31-59
days',31,59);
insert into termgroup (termgroupname,mindays,maxdays) values ('60-89
days',60,89);
insert into termgroup (termgroupname,mindays,maxdays) values ('90-119
days',90,119);
insert into termgroup (termgroupname,mindays,maxdays) values ('120-179
days',120,179);
insert into termgroup (termgroupname,mindays,maxdays) values ('180-364
days',180,364);
insert into termgroup (termgroupname,mindays,maxdays) values ('1-2
years',365,729);
insert into termgroup (termgroupname,mindays,maxdays) values ('2-3
years',730,1094);
insert into termgroup (termgroupname,mindays,maxdays) values ('3-4
years',1095,1459);
insert into termgroup (termgroupname,mindays,maxdays) values ('4-5
years',1460,1824);
insert into termgroup (termgroupname,mindays,maxdays) values ('5+
years',1825,999999);
--
select * from termgroup order by mindays;
--

create table certgroups (
days int not null primary key,
number int not null,
termgroupname varchar(20) null);
--
insert into certgroups(days,number) values (25,123);
insert into certgroups(days,number) values (58,66);
insert into certgroups(days,number) values (303,11);
insert into certgroups(days,number) values (732,1056);
insert into certgroups(days,number) values (1096,66);
--
select * from certgroups order by days;
--
update certgroups
set termgroupname = tg.termgroupname
from certgroups c, termgroup tg
where (c.days >= tg.mindays) and (c.days <= tg.maxdays);
--
select * from certgroups order by days;
--
/*
-- MS-SQL output (CORRECT):
days        number      termgroupname
----------- ----------- --------------------
58          66          31-59 days
303         11          180-364 days
732         1056        2-3 years
1096        66          3-4 years

(4 row(s) affected)

-- PostgreSQL output (WRONG!!):
days | number | termgroupname
------+--------+---------------58 |     66 | 31-59 days         OK303 |     11 | 31-59 days         WRONG732 |   1056 |
31-59days         WRONG
 
1096 |     66 | 31-59 days         WRONG
(4 rows)
*/

insert into certgroups(days,number) values (25,123);
--
update certgroups
set termgroupname = tg.termgroupname
from certgroups c, termgroup tg
where (c.days >= tg.mindays) and (c.days <= tg.maxdays);
--
select * from certgroups order by days;
--
/*
-- MS-SQL output (CORRECT):
days        number      termgroupname
----------- ----------- --------------------
25          123         1-30 days
58          66          31-59 days
303         11          180-364 days
732         1056        2-3 years
1096        66          3-4 years

(5 row(s) affected)

-- PostgreSQL output (WRONG!!):
days | number | termgroupname
------+--------+---------------25 |    123 | 1-30 days    RIGHT58 |     66 | 1-30 days    WRONG303 |     11 | 1-30 days
  WRONG732 |   1056 | 1-30 days    WRONG
 
1096 |     66 | 1-30 days    WRONG
(5 rows)
*/
----------- END of SQL session

On Fri, 22 Aug 2008, Steve Johnson wrote:

> update certgroups
> set termgroupname = tg.termgroupname
> from certgroups c, termgroup tg
> where (c.days >= tg.mindays) and (c.days <= tg.maxdays);

In recent PostgreSQL versions I believe this is properly written:

update certgroups c
set termgroupname = tg.termgroupname
from termgroup tg
where (c.days >= tg.mindays) and (c.days <= tg.maxdays);

At least as of SQL2003, I think both of the above use extensions, so
there's no guarantee to the behavior on different systems and to do it
with a standard query, you'd need to use a subselect, something like:

update certgroups c set termgroupname = (select termgroupname from
termgroup tg where (c.days >= tg.mindays) and (c.days <=tg.maxdays));

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 22 Aug 2008, Steve Johnson wrote:
>> update certgroups
>> set termgroupname = tg.termgroupname
>> from certgroups c, termgroup tg
>> where (c.days >= tg.mindays) and (c.days <= tg.maxdays);

> In recent PostgreSQL versions I believe this is properly written:
> update certgroups c
> set termgroupname = tg.termgroupname
> from termgroup tg
> where (c.days >= tg.mindays) and (c.days <= tg.maxdays);

Yeah, in PG's eyes the former is creating a cartesian join between two
versions of certgroups.  I think MSSQL interprets the FROM reference as
being the same as the update target, but we don't.

> At least as of SQL2003, I think both of the above use extensions,

Correct, the standard disallows a FROM clause altogether; and I'm not
sure that they weren't right.  No matter which way you resolve the above
ambiguity, you've still got the problem that the update behavior is
ill-defined if a given target row joins to more than one set of rows
from the other table(s).
        regards, tom lane