Thread: 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
> 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...? SELECTt2.foo + t2.bar FROM( SELECT field1 / 2 AS foo, field2 * 2 AS bar FROM table1 t1 WHERE foo < 12) ASt2 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
> 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
>
First, I think it would be great if this worked - like the alias to an
update table added in 8.2 - saves a lot of typing and makes queries
much more readable.
Second, only way I see is to set this on top of a view generating your
foo and bar aliases, and go from there. Compared to rewriting the
expression each time, it has the advantages of better syntax in the end
and might yield better performance as well.
____________________________________________________________________________________
Got a little couch potato?
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
chester c young <chestercyoung@yahoo.com> writes:
>> SELECT field1 / 2 AS foo,
>> field2 * 2 AS bar,
>> foo + bar AS total
>> WHERE foo < 12;
> First, I think it would be great if this worked - like the alias to an
> update table added in 8.2 - saves a lot of typing and makes queries
> much more readable.
This is not an "extension", it is *directly* contrary to both the letter
and spirit of the SQL standard. I can hardly believe that M$ did that
... oh, actually, I can entirely believe it. The OP has a serious
problem of vendor lockin now, and that's exactly what M$ wants.
regards, tom lane
On Jul 12, 2007, at 1:23 PM, Tom Lane wrote: > chester c young <chestercyoung@yahoo.com> writes: >>> SELECT field1 / 2 AS foo, >>> field2 * 2 AS bar, >>> foo + bar AS total >>> WHERE foo < 12; > >> First, I think it would be great if this worked - like the alias >> to an >> update table added in 8.2 - saves a lot of typing and makes queries >> much more readable. > > This is not an "extension", it is *directly* contrary to both the > letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. > > regards, tom lane Hear hear! What's really screwy is what I found when I hooked access into my PostgreSQL database using pgsqlODBC (I know, it's an abomination) and I logged the statements that PostgreSQL was processing. In MS Access this query: SELECT foo AS bar, bar * 2 AS gleep FROM table; became this in PostgreSQL's logs SELECT foo FROM table; Vewwy Intewesting! I think Microsoft and ODBC might be making extra work for themselves (obviously they are since they are allowing these aliases) Or maybe not. Maybe I can glean something from this. Who knows. --Joel
Joel Richard <postgres@joelrichard.com> writes:
> What's really screwy is what I found when I hooked access into my
> PostgreSQL database using pgsqlODBC (I know, it's an abomination) and
> I logged the statements that PostgreSQL was processing. In MS Access
> this query:
> SELECT foo AS bar, bar * 2 AS gleep FROM table;
> became this in PostgreSQL's logs
> SELECT foo FROM table;
> Vewwy Intewesting!
Yeah, that *is* interesting --- it means Access is trying to do all the
arithmetic for itself. I guess this means that you could keep using
the statements unchanged if you wanted to use Access as a frontend to
Postgres. Of course you're trying to get away from that, but this
might at least give you a chance of fixing things incrementally instead
of having to fix everything all at once.
regards, tom lane
Joel,
To avoid ms access from managing the query, use a pass-through query. Then access will send the raw sql statment to
psqland psql will return just the results to access.
It will speed things up a bit too for large datasets.
Phillip allen
Sent via BlackBerry by AT&T
-----Original Message-----
From: Joel Richard <postgres@joelrichard.com>
Date: Thu, 12 Jul 2007 13:36:05
To:Tom Lane <tgl@sss.pgh.pa.us>
Cc:sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: [SQL] Converting from MS Access field aliases
On Jul 12, 2007, at 1:23 PM, Tom Lane wrote:
> chester c young <chestercyoung@yahoo.com> writes:
>>> SELECT field1 / 2 AS foo,
>>> field2 * 2 AS bar,
>>> foo + bar AS total
>>> WHERE foo < 12;
>
>> First, I think it would be great if this worked - like the alias
>> to an
>> update table added in 8.2 - saves a lot of typing and makes queries
>> much more readable.
>
> This is not an "extension", it is *directly* contrary to both the
> letter
> and spirit of the SQL standard. I can hardly believe that M$ did that
> ... oh, actually, I can entirely believe it. The OP has a serious
> problem of vendor lockin now, and that's exactly what M$ wants.
>
> regards, tom lane
Hear hear!
What's really screwy is what I found when I hooked access into my
PostgreSQL database using pgsqlODBC (I know, it's an abomination) and
I logged the statements that PostgreSQL was processing. In MS Access
this query:
SELECT foo AS bar, bar * 2 AS gleep FROM table;
became this in PostgreSQL's logs
SELECT foo FROM table;
Vewwy Intewesting! I think Microsoft and ODBC might be making extra
work for themselves (obviously they are since they are allowing these
aliases) Or maybe not. Maybe I can glean something from this. Who knows.
--Joel
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Thu, 2007-07-12 at 13:23 -0400, Tom Lane wrote: > chester c young <chestercyoung@yahoo.com> writes: > >> SELECT field1 / 2 AS foo, > >> field2 * 2 AS bar, > >> foo + bar AS total > >> WHERE foo < 12; > > First, I think it would be great if this worked - like the alias to an > > update table added in 8.2 - saves a lot of typing and makes queries > > much more readable. > This is not an "extension", it is *directly* contrary to both the letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. "AS" works in Informix, and I believe, in DB2 as well. So it is at least pretty common; I'm not saying it is correct. Since Informix predates M$-SQL they at least didn't invent it.
"Adam Tauno Williams" <adamtaunowilliams@gmail.com> writes: > "AS" works in Informix, and I believe, in DB2 as well. So it is at > least pretty common; I'm not saying it is correct. Since Informix > predates M$-SQL they at least didn't invent it. AS works in Postgres too. But the defined aliases are only in the resulting output columns, not in scope for the where clause. In fact the expressions used aren't even evaluated for rows which don't match the where clause which is pretty important if those expressions are subqueries or volatile functions. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Adam Tauno Williams <adamtaunowilliams@gmail.com> writes:
> On Thu, 2007-07-12 at 13:23 -0400, Tom Lane wrote:
>> chester c young <chestercyoung@yahoo.com> writes:
>>> SELECT field1 / 2 AS foo,
>>> field2 * 2 AS bar,
>>> foo + bar AS total
>>> WHERE foo < 12;
>> This is not an "extension", it is *directly* contrary to both the letter
>> and spirit of the SQL standard.
> "AS" works in Informix, and I believe, in DB2 as well.
I can't speak to Informix, but I will bet a good lunch that it does not
work like that in DB2. DB2 is one of the more nearly
standard-conformant databases on the planet. (Or possibly it's the
other way around ... there seem to be a lot of IBMers on the committee.)
regards, tom lane
Tom Lane skrev: > This is not an "extension", it is *directly* contrary to both the letter > and spirit of the SQL standard. I can hardly believe that M$ did that > ... oh, actually, I can entirely believe it. The OP has a serious > problem of vendor lockin now, and that's exactly what M$ wants. Don't attribute to malice what can be adequately explained by incompetence. Nis
On 7/13/07, Nis Jørgensen <nis@superlativ.dk> wrote: > Don't attribute to malice what can be adequately explained > by incompetence. He didn't :) > Nis Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm
* Tom Lane <tgl@sss.pgh.pa.us> wrote: Hi, > This is not an "extension", it is *directly* contrary to both > the letter and spirit of the SQL standard. at which point is this breaking the specification ? What would happen if postgres would allow this ? IMHO supporting aliases in where clauses would make some queries easier to read. Think of cases where some column is coming from an complex calculation (ie. many nested CASE'es, etc, etc) and you need that calculated column in the WHERE clause. Of course it's good style to encode those things in views, but is this always very performant ? cu -- ---------------------------------------------------------------------Enrico Weigelt == metux IT service - http://www.metux.de/ ---------------------------------------------------------------------Please visit the OpenSource QM Taskforce: http://wiki.metux.de/public/OpenSource_QM_TaskforcePatches/ Fixes for a lot dozens of packages in dozens of versions:http://patches.metux.de/ ---------------------------------------------------------------------