From: | "btober(at)computer(dot)org" <btober(at)broadstripe(dot)net> |
---|---|
To: | Howard News <howardnews(at)selestial(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select function alias |
Date: | 2016-11-30 15:15:26 |
Message-ID: | 464073177.95002308.1480518926815.JavaMail.zimbra@broadstripe.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
----- Original Message -----
> From: "Howard News" <howardnews(at)selestial(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2016-11-30 15:28:21 | Re: "Fuzzy" Matches on Nicknames |
Previous Message | Adrian Klaver | 2016-11-30 14:43:21 | Re: Index size |