Skip site navigation (1) Skip section navigation (2)

Re: Urgent help needed- alias name in update statement

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Michael Wood <esiotrot(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Urgent help needed- alias name in update statement
Date: 2010-03-09 18:02:03
Message-ID: 4ec1cf761003091002w7c10217ayb0a393f4934384df@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
On Tue, Mar 9, 2010 at 10:42 AM, Michael Wood <esiotrot(at)gmail(dot)com> wrote:
[snip]
> I don't know why the above doesn't work.  I've encountered something
> in the past which may be related:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND LOWER(SPLIT_PART(something, '^', 3)) <> ''
>    AND other = 123;
>
> This works, but what I want to do is the following:
>
>    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
>    FROM mytable
>    WHERE something IS NOT NULL
>    AND blah <> ''
>    AND other = 123;
>
> This does not work and I don't know why not.

This behavior is mandated by the SQL standard, I believe. I'm too lazy
to dig up the actual reference, but for instance
http://dev.mysql.com/doc/refman/5.0/en//problems-with-alias.html
claims:
    Standard SQL disallows references to column aliases in a WHERE clause.
    This restriction is imposed because when the WHERE clause is evaluated,
    the column value may not yet have been determined...

You could workaround by using a subquery like:

SELECT mysubq.blah FROM (
    SELECT LOWER(SPLIT_PART(something, '^', 3)) AS blah
    FROM mytable
    WHERE something IS NOT NULL
    AND other = 123
)  AS mysubq
WHERE mysubq.blah <> '' ;

Josh

In response to

Responses

pgsql-novice by date

Next:From: venkatrao.bDate: 2010-03-10 04:20:23
Subject: Re: Urgent help needed- alias name in update statement
Previous:From: Steve TDate: 2010-03-09 15:55:54
Subject: Re: Urgent help needed- alias name in update statement

pgsql-general by date

Next:From: Thomas KellererDate: 2010-03-09 18:09:35
Subject: Re: \copy command: how to define a tab character as the delimiter
Previous:From: Tom LaneDate: 2010-03-09 18:00:42
Subject: Re: Update view/table rule order of operations or race condition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group