select function alias

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.

----- Original Message -----

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

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general