Re: When did this behavior change (and where else might it bite me)? - Mailing list pgsql-general

On Mar 18, 2013, at 9:49 AM, Jeff Amiel <becauseimjeff@yahoo.com> wrote:

> In prepping for an upgrade to 9.2.3, I stumbled across this:
>
> CREATE TABLE foo
> (
>   myint integer,
>   string1 text,
>   string2 text
> )
> WITH (
>   OIDS=FALSE
> );
>
> insert into foo values (12345,'Y','N');
>
>  select * from foo f where f.myint = 12345 or f.name='Y'
>
> In 9.2.3, this returns:
> ERROR:  column f.name does not exist
> LINE 1:  select * from foo f where myint = 12345 or f.name='Y'
>
> in 8.4.6 ,this returns no error (and gives me the row from the table)

That's (unintentionally) an attribute style data type cast - bar.name is the same as name(bar), and tries to cast bar
totype "name" (an internal-use string type)  

Try "select foo from foo", "select name(foo::text) from foo" and "select name(foo) from foo" to see what's going on.

That was tightened up in 9.1, I think:

    Casting

    Disallow function-style and attribute-style data type casts for composite types (Tom Lane)

    For example, disallow composite_value.text and text(composite_value). Unintentional uses of this syntax have
frequentlyresulted in bug reports; although it was not a bug, it seems better to go back to rejecting such expressions.
TheCASTand :: syntaxes are still available for use when a cast of an entire composite value is actually intended. 

Cheers,
  Steve