Re: 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 OK
303 | 11 | 31-59 days WRONG
732 | 1056 | 31-59 days 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 RIGHT
58 | 66 | 1-30 days WRONG
303 | 11 | 1-30 days WRONG
732 | 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(at)megazone(dot)bigpanda(dot)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