Postgres Pro
Downloads
Home   >   mailing lists

Thread: can not use the column after rename

can not use the column after rename

From
salah jubeh
Date:
Hello,

Why I can not do something like this in Postgres.

 SELECT 1 as a , 2 as b , a + b as c ;

Regards
 

Re: can not use the column after rename

From
Simon Tokumine
Date:
Hi Salah,

This is equivalent:

WITH numbers AS (SELECT 1 AS a, 2 AS b) SELECT a, b, a+b AS c FROM numbers;

S

On Wed, Dec 21, 2011 at 11:39 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
Hello,

Why I can not do something like this in Postgres.

 SELECT 1 as a , 2 as b , a + b as c ;

Regards
 


Re: can not use the column after rename

From
salah jubeh
Date:

Hello Simon,

This question was raised up during writing a query where many columns are a result of long mathematical operations, so I thought why not to rename them and use the new names  instead of repeating the same operations

On the row level,  the value of a and b are know, so why we need a temporary table or CTE. Why I can not use it directly  as shown in the example below. why a and b can not be used as aliases for the column names ? I want to know the theoretical reason behind it?

Thanks


CREATE TABLE numbers (
    num1 integer,
    num2 integer
 );

 INSERT INTO numbers values (3,4);

 SELECT num1, num2, num1 + num2 FROM numbers

  SELECT num1 as a, num2 as b, a + b as c FROM number
  SELECT num1 as a, num2 as b, num1 + num2 as c FROM numbers



 

From: Simon Tokumine <simon@vizzuality.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Wednesday, December 21, 2011 1:14 PM
Subject: Re: [GENERAL] can not use the column after rename

Hi Salah,

This is equivalent:

WITH numbers AS (SELECT 1 AS a, 2 AS b) SELECT a, b, a+b AS c FROM numbers;

S

On Wed, Dec 21, 2011 at 11:39 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
Hello,

Why I can not do something like this in Postgres.

 SELECT 1 as a , 2 as b , a + b as c ;

Regards
 




Re: can not use the column after rename

From
Marti Raudsepp
Date:
On Wed, Dec 21, 2011 at 15:24, salah jubeh <s_jubeh@yahoo.com> wrote:
> Why I can not use it directly  as shown in the example below.
> why a and b can not be used as aliases for the column names ? I want to know
> the theoretical reason behind it?

As far as I know, that's how the SQL standard specifies it. PostgreSQL
is pretty strict about following the SQL standard. Diverging from the
standard in this aspect would cause ambiguities.

Regards,
Marti

Re: can not use the column after rename

From
"David Johnston"
Date:

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of salah jubeh
Sent: Wednesday, December 21, 2011 6:39 AM
To: pgsql
Subject: [GENERAL] can not use the column after rename

 

Hello,

 

Why I can not do something like this in Postgres.

 

 SELECT 1 as a , 2 as b , a + b as c ;

 

Regards

 

 

In a declarative language same-level cross-references are difficult to process (though admittedly not impossible).  What if you had written:

 

SELECT a + b AS c,  1 as a,  2 as b

 

If scanned in a linear (top-to-bottom) fashion A and B would not be defined at the time the “a + b” part was evaluated.

 

David J.

Re: can not use the column after rename

From
Tom Lane
Date:
salah jubeh <s_jubeh@yahoo.com> writes:
> On the row level,� the value of a and b are know, so why we need a temporary table or CTE. Why I can not use it
directly�as shown in the example below. why a and b can not be used as aliases for the column names ? I want to know
thetheoretical reason behind it? 

The SQL standard envisions all the expressions in a SELECT list being
computed concurrently and independently.  So they can't refer to each
other.  If they could, it would introduce ambiguity.  Consider a table
t that provides columns x,y,z, and suppose we have

    SELECT x AS z, y, y+z AS sum FROM t;

If cross-references were allowed, it would be unclear what the last "z"
is supposed to refer to.

            regards, tom lane