[SQL] bug in 9.2.2 ? subquery accepts wrong column name : upd

2013-03-14 Thread Achilleas Mantzios 
dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM 
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY 
sst.setid ORDER BY character_length(vsl.name))) as arragg 
FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY 
sst.setid) as qry 
 WHERE array_length(qry.arragg,1)>1  AND qry.setid IN (SELECT setid from 
sis_oper_cons) ORDER BY qry.setid,array_length(qry.arragg,1);
 setid |  arragg  
---+--
54 | {EQUZZZ,SAMZZZ}
55 | {"ZZZR","ZZZTRAVEL"}
81 | {"ZZZ SISTER","ZZZ DUMMY II"}
(3 rows)

however, there is not column setid in sis_oper_cons,

dynacom=# SELECT setid from sis_oper_cons;
ERROR:  column "setid" does not exist
LINE 1: SELECT setid from sis_oper_cons;
   ^
9.2.2 Postgresql treats qry.setid IN (SELECT setid from sis_oper_cons) as true.
However, making subquery look like (SELECT soc.setid from sis_oper_cons soc), 
as in 

dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM 
(select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY sst.setid 
ORDER BY character_length(vsl.name))) as arragg FROM sissets sst, vessels vsl 
WHERE vsl.id=sst.vslid ORDER BY sst.setid) as qry WHERE 
array_length(qry.arragg,1)>1  AND qry.setid NOT IN (SELECT soc.setid from 
sis_oper_cons soc) ORDER BY qry.setid,array_length(qry.arragg,1);
ERROR:  column soc.setid does not exist
LINE 1: ...gth(qry.arragg,1)>1  AND qry.setid NOT IN (SELECT soc.setid ...
 ^
dynacom=# 

postgresql corerctly identifies and throws the error.

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] bug in 9.2.2 ? subquery accepts wrong column name : upd

2013-03-14 Thread Tom Lane 
Achilleas Mantzios  writes:
> dynacom=# SELECT DISTINCT ON (qry.setid) qry.setid, qry.arragg[1:2] FROM 
>   (select distinct sst.setid,(array_agg(vsl.name) OVER (PARTITION BY 
> sst.setid ORDER BY character_length(vsl.name))) as arragg 
>   FROM sissets sst, vessels vsl WHERE vsl.id=sst.vslid ORDER BY 
> sst.setid) as qry 
>  WHERE array_length(qry.arragg,1)>1  AND qry.setid IN (SELECT setid from 
> sis_oper_cons) ORDER BY qry.setid,array_length(qry.arragg,1);
> [ works ]

> however, there is not column setid in sis_oper_cons,

If not, that's a perfectly legal outer reference to qry.setid.

Probably not one of SQL's better design features, since it confuses
people regularly; but it's required by spec to work like that.

regards, tom lane


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] UPDATE query with variable number of OR conditions in WHERE

2013-03-14 Thread JORGE MALDONADO 
I am building an UPDATE query at run-time and one of the fields I want to
include in the WHERE condition may repeat several times, I do not know how
many.

UPDATE table1
SET field1 = "some value"
WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n)

I build such a query using a programming language and, after that, I
execute it. Is this a good approach to build such a query?

Respectfully,
Jorge Maldonado

Re: [SQL] UPDATE query with variable number of OR conditions in WHERE

2013-03-14 Thread Ben Morrow 
Quoth jorgemal1...@gmail.com (JORGE MALDONADO):
> 
> I am building an UPDATE query at run-time and one of the fields I want to
> include in the WHERE condition may repeat several times, I do not know how
> many.
> 
> UPDATE table1
> SET field1 = "some value"
> WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n)
> 
> I build such a query using a programming language and, after that, I
> execute it. Is this a good approach to build such a query?

You can use IN for this:

UPDATE table1
SET field1 = "some value"
WHERE field2 IN (value_1, value_2, ...);

Ben



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] UPDATE query with variable number of OR conditions in WHERE

2013-03-14 Thread Tom Lane 
Ben Morrow  writes:
> Quoth jorgemal1...@gmail.com (JORGE MALDONADO):
>> I am building an UPDATE query at run-time and one of the fields I want to
>> include in the WHERE condition may repeat several times, I do not know how
>> many.
>> 
>> UPDATE table1
>> SET field1 = "some value"
>> WHERE (field2 = value_1 OR field2 = value_2 OR .OR field2 = value_n)
>> 
>> I build such a query using a programming language and, after that, I
>> execute it. Is this a good approach to build such a query?

> You can use IN for this:

> UPDATE table1
> SET field1 = "some value"
> WHERE field2 IN (value_1, value_2, ...);

IN is definitely better style than a long chain of ORs.  Another
possibility is to use = ANY(ARRAY):

UPDATE table1
SET field1 = "some value"
WHERE field2 = ANY (ARRAY[value_1, value_2, ...]);

This is not better than IN as-is (in particular, IN is SQL-standard and
this is not), but it opens the door to treating the array of values as a
single parameter:

UPDATE table1
SET field1 = "some value"
WHERE field2 = ANY ($1::int[]);

(or text[], etc).  Now you can build the array client-side and not need
a new statement for each different number of comparison values.  If
you're not into prepared statements, this may not excite you, but some
people find it to be a big deal.

regards, tom lane


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql