From: | "Nicholas Barr" <nicky(at)chuckie(dot)co(dot)uk> |
---|---|
To: | "Joel Richard" <postgres(at)joelrichard(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Converting from MS Access field aliases |
Date: | 2007-07-12 15:55:16 |
Message-ID: | 56317.62.244.190.66.1184255716.squirrel@www.chuckie.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | chester c young | 2007-07-12 16:36:50 | Re: Converting from MS Access field aliases |
Previous Message | Tom Lane | 2007-07-12 15:34:44 | Re: Using case or if to return multiple rows |