Re: Converting from MS Access field aliases

> Good morning,
>
> Oh joyous day! We are upgrading a legacy database system from MS
> Access to PostgreSQL! Yay!
>
> Ok, rejoicing over. Here's our issue and PLEASE point me to the right
> place if this has been discussed before.
>
> In MS Access one can reuse field aliases later in the same query. For
> example:
>
> SELECT field1 / 2 AS foo,
> field2 * 2 AS bar,
> foo + bar AS total
> WHERE foo < 12;
>
> The first two fields are fine, it's the third that's a problem. The
> database reports
>
> ERROR: column "foo" does not exist
>
> This type of situation is happening -many- times in well over fifty
> existing SELECT..INTO and INSERT INTO queries. The obvious solution
> here is to copy the code around to eliminate the need to reuse "foo"
> and "bar" in the query:
>
> SELECT field1 / 2 AS foo,
> field2 * 2 AS bar,
> (field1 / 2) + (field2 * 2) AS total
> WHERE (field1 / 2) < 12;
>
> But this is a bit ugly and cumbersome and in our case, not desirable
> since foo and bar get used many times in the remains of the query. To
> replace them with the formulae means that debugging is quite
> difficult and very prone to errors.
>
> Does anyone have suggestions on how to circumvent this in a more
> graceful manner? I mean I could probably find a way to do this with a
> couple of queries and some views, or maybe write a function (or more
> like 30 functions) to do the work, but both of those only add to the
> workload in an undesirable manner. :)
>
> To complicate matters, performance is a concern. We're operating on
> upwards of a billion records. Not all at the same time, but the goal
> is to run these a series of calculations will be done on all of the
> data.
>
> Thanks for any input that you might have.
>
> --Joel

Are you able to restructure your queries to be something like...?

SELECT
t2.foo + t2.bar
FROM
(
SELECT
field1 / 2 AS foo,
field2 * 2 AS bar
FROM
table1 t1
WHERE
foo < 12
) AS t2

PG allows sub-clauses and statements in the FROM clause, as well as in the
WHERE & SELECT clauses.

Not sure how these will perform on a billion rows, so a few EXPLAIN
ANALYSE outputs might help tune the queries some more if you have them.
The results should be semantically comparable to the MS Access queries
though (I think).

Nick