Re: select function alias

["btober@xxxxxxxxxxxx" <btober@xxxxxxxxxxxxxxx>]

---

From: "Howard News" <howardnews@xxxxxxxxxxxxx>
Sent: Wednesday, November 30, 2016 9:03:28 AM
Subject: Re: 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