pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConverting from MS Access field aliases
13+ messages / 10 participants
[nested] [flat]
* Converting from MS Access field aliases
@ 2007-07-12 14:07 Joel Richard <postgres@joelrichard.com>
0 siblings, 2 replies; 13+ messages in thread
From: Joel Richard @ 2007-07-12 14:07 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 15:55 Nicholas Barr <nicky@chuckie.co.uk>
parent: Joel Richard <postgres@joelrichard.com>
1 sibling, 0 replies; 13+ messages in thread
From: Nicholas Barr @ 2007-07-12 15:55 UTC (permalink / raw)
To: Joel Richard <postgres@joelrichard.com>; +Cc: 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 16:36 chester c young <chestercyoung@yahoo.com>
parent: Joel Richard <postgres@joelrichard.com>
1 sibling, 1 reply; 13+ messages in thread
From: chester c young @ 2007-07-12 16:36 UTC (permalink / raw)
To: Joel Richard <postgres@joelrichard.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 17:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 4 replies; 13+ messages in thread
From: Tom Lane @ 2007-07-12 17:23 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: Joel Richard <postgres@joelrichard.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 17:36 Joel Richard <postgres@joelrichard.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 2 replies; 13+ messages in thread
From: Joel Richard @ 2007-07-12 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 17:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joel Richard <postgres@joelrichard.com>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2007-07-12 17:48 UTC (permalink / raw)
To: Joel Richard <postgres@joelrichard.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 18:07 paallen@attglobal.net
parent: Joel Richard <postgres@joelrichard.com>
1 sibling, 0 replies; 13+ messages in thread
From: paallen@attglobal.net @ 2007-07-12 18:07 UTC (permalink / raw)
To: Joel Richard <postgres@joelrichard.com>; pgsql-sql-owner@postgresql.org, "Tom Lane" <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Joel,
To avoid ms access from managing the query, use a pass-through query. Then access will send the raw sql statment to psql and 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 20:34 Adam Tauno Williams <adamtaunowilliams@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 2 replies; 13+ messages in thread
From: Adam Tauno Williams @ 2007-07-12 20:34 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 22:00 Gregory Stark <stark@enterprisedb.com>
parent: Adam Tauno Williams <adamtaunowilliams@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Gregory Stark @ 2007-07-12 22:00 UTC (permalink / raw)
To: adamtaunowilliams@gmail.com; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-12 22:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam Tauno Williams <adamtaunowilliams@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2007-07-12 22:48 UTC (permalink / raw)
To: adamtaunowilliams@gmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-13 06:36 Nis Jørgensen <nis@superlativ.dk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 13+ messages in thread
From: Nis Jørgensen @ 2007-07-13 06:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-07-14 01:42 Andrej Ricnik-Bay <andrej.groups@gmail.com>
parent: Nis Jørgensen <nis@superlativ.dk>
0 siblings, 0 replies; 13+ messages in thread
From: Andrej Ricnik-Bay @ 2007-07-14 01:42 UTC (permalink / raw)
To: Nis Jørgensen <nis@superlativ.dk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Converting from MS Access field aliases
@ 2007-08-08 12:28 Enrico Weigelt <weigelt@metux.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 13+ messages in thread
From: Enrico Weigelt @ 2007-08-08 12:28 UTC (permalink / raw)
To: pgsql-sql
* 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_Taskforce
Patches / Fixes for a lot dozens of packages in dozens of versions:
http://patches.metux.de/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-08-08 12:28 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-12 14:07 Converting from MS Access field aliases Joel Richard <postgres@joelrichard.com>
2007-07-12 15:55 ` Nicholas Barr <nicky@chuckie.co.uk>
2007-07-12 16:36 ` chester c young <chestercyoung@yahoo.com>
2007-07-12 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-12 17:36 ` Joel Richard <postgres@joelrichard.com>
2007-07-12 17:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-12 18:07 ` paallen@attglobal.net
2007-07-12 20:34 ` Adam Tauno Williams <adamtaunowilliams@gmail.com>
2007-07-12 22:00 ` Gregory Stark <stark@enterprisedb.com>
2007-07-12 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-13 06:36 ` Nis Jørgensen <nis@superlativ.dk>
2007-07-14 01:42 ` Andrej Ricnik-Bay <andrej.groups@gmail.com>
2007-08-08 12:28 ` Enrico Weigelt <weigelt@metux.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox