>>>>> "Vladimir" == Vladimir Ryabtsev <greatvovan@xxxxxxxxx> writes: >> The workaround is to do it like this instead: Vladimir> Strange, I tried to do like this, but the first thing came Vladimir> into my mind was array_agg() not array(): Vladimir> delete from log Vladimir> where ctid = any( Vladimir> select array_agg(ctid) from ( Vladimir> select ctid from log Vladimir> where timestamp < now() at time zone 'pst' - interval '2 month' Vladimir> limit 10 Vladimir> ) v); Vladimir> This query complained like this: Vladimir> ERROR: operator does not exist: tid = tid[] Vladimir> LINE 2: where ctid = any( Vladimir> ^ Vladimir> HINT: No operator matches the given name and argument Vladimir> type(s). You might need to add explicit type casts. Vladimir> Which is strange because both array(select ...) and select Vladimir> array_agg() ... return the same datatype ctid[]. It's not so strange when you understand what's going on here. The fundamental issue is that "ANY" has two meanings in PG, one of them following the SQL standard and one not: x <operator> ANY (<subselect>) -- standard x <operator> ANY (<expression>) -- PG-specific In the first case, the behavior follows the standard, which makes this a generalization of IN: specifically, in the standard, x IN (select ...) is just alternative syntax for x = ANY (select ...) Obviously in this form, the result of the subselect is expected to be of the same type and degree as "x", hence the error since tid and tid[] are not the same type. (Because this is the standard form, it's the one chosen when the syntax is otherwise ambiguous between the two.) The form x = ANY (somearray) is a PG extension, but because of the ambiguity, the array can only be specified by something that doesn't parse as a select. So array() works (as does array[] for the commonly used case of an explicit list), but if you want to use a select to get the array value, you have to add some kind of syntax that makes it not parse as a select, e.g.: WHERE ctid = ANY ((select array_agg(...) from ...)::tid[]) In this case the cast forces it to parse as an expression and not a subquery (it's not enough to just use the parens alone, because PG, again unlike the SQL standard, allows any number of excess parens around a subquery). -- Andrew (irc:RhodiumToad)
