Postgres Pro
Downloads
Home   >   mailing lists

Thread: select function alias

select function alias

From
Timoteo Blanco
Date:
Howdy,

I've a series of timestamp columns I'd like to alias in select statements. psql indicates my alias doesnt exist after 
I define it. 

Example -> select to_char(impressions_create_date,'yyyy-mm-dd') as ymd from impressionsdb where ymd like '2016-11%' ;

psql always complains column ymd does not exist. I've inherited a series of tables with many timestamps and would like to 
shorten the select in queries without renaming any columns.


postgresql 9.2 on Gentoo 4.4.26

TIA, TRB

Re: select function alias

From
Howard News
Date:



On 30/11/2016 13:42, Timoteo Blanco wrote:
Howdy,

I've a series of timestamp columns I'd like to alias in select statements. psql indicates my alias doesnt exist after 
I define it. 

Example -> select to_char(impressions_create_date,'yyyy-mm-dd') as ymd from impressionsdb where ymd like '2016-11%' ;

psql always complains column ymd does not exist. I've inherited a series of tables with many timestamps and would like to 
shorten the select in queries without renaming any columns.


postgresql 9.2 on Gentoo 4.4.26

TIA, TRB


3 options:
  1. Use the same to_char expression in the where clause
  2. Use a sub-select to use the alias in the outer where clause
  3. Use the original column in the where clause and use the timestamp comparisson functions.

Howard.

Re: select function alias

From
"btober@computer.org"
Date:
From: "Howard News" <howardnews@selestial.com>
Sent: Wednesday, November 30, 2016 9:03:28 AM
Subject: Re: [GENERAL] select function alias


On 30/11/2016 13:42, Timoteo Blanco wrote:
I've a series of timestamp columns I'd like to alias in select statements. psql indicates my alias doesnt exist after 
I define it. 

Example -> select to_char(impressions_create_date,'yyyy-mm-dd') as ymd from impressionsdb where ymd like '2016-11%' ;

psql always complains column ymd does not exist. I've inherited a series of tables with many timestamps and would like to 
shorten the select in queries without renaming any columns.


3 options:
  1. Use the same to_char expression in the where clause
  2. Use a sub-select to use the alias in the outer where clause
  3. Use the original column in the where clause and use the timestamp comparisson functions.


4. Define views on the tables. Make the views writeable using rules.


5. Use a CTE:

    with shortnames as (
        select to_char(impressions_create_date,'yyyy-mm-dd') as ymd from impressionsdb 
) select ymd from shortnames where ymd like '2016-11%';


-- B


Re: select function alias

From
"David G. Johnston"
Date:
On Wed, Nov 30, 2016 at 8:15 AM, btober@computer.org <btober@broadstripe.net> wrote:
5. Use a CTE:

    with shortnames as (
        select to_char(impressions_create_date,'yyyy-mm-dd') as ymd from impressionsdb 
) select ymd from shortnames where ymd like '2016-11%';


​Except that WHERE clause won't get pushed down (I think...haven't checked an explain recently) so a sub-select is definitely better in this scenario.

David J.

Re: select function alias

From
Merlin Moncure
Date:
On Wed, Nov 30, 2016 at 12:15 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Wed, Nov 30, 2016 at 8:15 AM, btober@computer.org
> <btober@broadstripe.net> wrote:
>>
>> 5. Use a CTE:
>>
>>     with shortnames as (
>>         select to_char(impressions_create_date,'yyyy-mm-dd') as ymd from
>> impressionsdb
>> ) select ymd from shortnames where ymd like '2016-11%';
>>
>
> Except that WHERE clause won't get pushed down (I think...haven't checked an
> explain recently) so a sub-select is definitely better in this scenario.

It's still the case FWICT.  I suspect CTE optimization fencing is
mostly considered a feature, not a bug.

merlin