8.3 vs 8.2 sql compatiblity issue

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 15 Nov 2007 12:03:27 -0600
Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

I am actually getting a lot of these operator does not exist errors
in 8.3 another one I get is operator does not exist for char=integer

This appears to be a classic example of:

#

Casts to text that formerly occurred implicitly may now need to be
written explicitly

Data types other than char and varchar are no longer implicitly
castable to text, except in the limited case of a || (concatenation)
operator whose other input is textual. While this will require explicit
casts in a few queries that didn't need them before, the elimination of
surprising interpretations justifies it.

- From the release notes :)

http://www.postgresql.org/docs/8.3/static/release-8-3.html

Sincerely,

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHPIvRATb/zqfZUUQRAsmpAJ9pKOwl2K5sGtulosfEcZuuCLaFSACcDyk8
CXZQidZVUA3Wn7vzC02yB8g=
=/PEp
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd@commandprompt.com> writes:

Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

I am actually getting a lot of these operator does not exist errors
in 8.3 another one I get is operator does not exist for char=integer

This appears to be a classic example of:
Casts to text that formerly occurred implicitly may now need to be
written explicitly

That's no doubt got something to do with it, but I think Tony is mighty
confused about exactly what is failing. pg_constraint.conkey is not
text, for instance; it's smallint[] and so the quoted bit should still
work just fine. I'd suggest trying the query in some client that gives
you an error location pointer, which whatever he's using evidently does
not.

regards, tom lane

Tom Lane wrote:

That's no doubt got something to do with it, but I think Tony is mighty
confused about exactly what is failing. pg_constraint.conkey is not
text, for instance; it's smallint[] and so the quoted bit should still
work just fine. I'd suggest trying the query in some client that gives
you an error location pointer, which whatever he's using evidently does
not.

regards, tom lane

You are exactly correct, I copied the wrong line in the original
message, sorry about that, it was this line:

case when a.attnum as text IN( select array_to_string(conkey,',') from
pg_constraint where

which is fixed by adding a cast:

case when cast(a.attnum as text) IN( select array_to_string(conkey,',')
from pg_constraint where

I must have missed it in the release notes about the implicit casts not
working anymore.

It's going to be a huge pain in the ass to go through all the code and
add explicit casts :-(

Thanks,

Tony

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:

it was this line:

case when a.attnum as text IN( select array_to_string(conkey,',') from
pg_constraint where

which is fixed by adding a cast:

case when cast(a.attnum as text) IN( select array_to_string(conkey,',')
from pg_constraint where

Surely that's the worst bit of SQL code I've seen in awhile. Why
*weren't* you using "a.attnum = any(conkey)"?? Performing this
operation textually is simply bogus.

regards, tom lane

Tom Lane wrote:

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:

case when cast(a.attnum as text) IN( select array_to_string(conkey,',')
from pg_constraint where

Surely that's the worst bit of SQL code I've seen in awhile.

Wow, you really are lucky.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Java is clearly an example of money oriented programming" (A. Stepanov)

Alvaro Herrera wrote:

Tom Lane wrote:

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:

case when cast(a.attnum as text) IN( select array_to_string(conkey,',')
from pg_constraint where

Surely that's the worst bit of SQL code I've seen in awhile.

Wow, you really are lucky.

You guys really should keep such kind words to yourself.
Not sure how in the hell you can say its bad code when it is just a
little piece.
You don't even know what it does.

Again thank you for you kind words of wisdom.

Have a great day.

Tony

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 15 Nov 2007 16:17:21 -0600
Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:

You guys really should keep such kind words to yourself.
Not sure how in the hell you can say its bad code when it is just a
little piece.

You pasted the entire SQL statement, that's how. It isn't hard to be
able to say, "Wow, that is a really bad piece of SQL" and frankly Tom
(not that he needs me to defend him) is one of the most (if not the
most) qualified people to make that statement on this list.

You don't even know what it does.

Tony, it is obvious that you do not get the FOSS way of doing things
in the least.

If you post publicly something that is that ugly, then it is going to
get critiqued. It is that simple. You don't like it, don't post.

I have more than once taken my beatings on this list. It is time for
you to either grow a thicker skin or unsubscribe.

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHPMrkATb/zqfZUUQRAgyOAJ42Oy+3/UKmJ5IErZCnEJGcPIm2qgCeMX6p
s9+5JIvhxPAPNyIeX+7+LtI=
=j4lt
-----END PGP SIGNATURE-----

Joshua D. Drake wrote:

in the least.

If you post publicly something that is that ugly, then it is going to
get critiqued. It is that simple. You don't like it, don't post.

I have more than once taken my beatings on this list. It is time for
you to either grow a thicker skin or unsubscribe.

Joshua D. Drake

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHPMrkATb/zqfZUUQRAgyOAJ42Oy+3/UKmJ5IErZCnEJGcPIm2qgCeMX6p
s9+5JIvhxPAPNyIeX+7+LtI=
=j4lt
-----END PGP SIGNATURE-----

I absolutely agree. The fact of the matter is that you came here asking
for help, and there are a number of very qualified very generous people
who are not only quite proficient with pgsql, but they are donating
their time to help you (in this case by letting you know your SQL was
poorly written) and to thank them with anger and sarcasm is not only
ungrateful, but quite rude. Not to mention the large number of people
who probably took the time to read that post only to find out that
they've wasted a (small, admittedly) portion of their day to read your
drivel.

The two things I've learned from this group are PostgreSQL is an
excellent piece of software, capable of many things, and the PostgreSQL
community is an amazing group, also capable of many things, one of which
is selflessly giving their time to help the users of this list. Even the
ungrateful ones like you, or the lazy ones that haven't heard of google,
or people like me. With a lack of experience and a lack of formal
training, this group has managed to help me build a decent data mine
solution, something I would have been unable to do without them.

Now that I'm done ranting, let me just reiterate. Thank you to everybody
who has helped me, and anybody else coming to this list seeking
guidance. I'm sorry that not everybody appreciates it.

On a side note, everytime I hit reply to try to post back to the list,
the reply-to is set to the original sender of the message, not the list
address, and since this is opposite behavior to the other mailing lists
I participate in, I often forget and send a message straight to the
previous poster. If I have sent a long diatribe meant for somebody else
to you, I apologize :-)

Tony,

* Tony Caduto (tony_caduto@amsoftwaredesign.com) wrote:

You guys really should keep such kind words to yourself. Not sure how in
the hell you can say its bad code when it is just a little piece. You don't
even know what it does.

Erm, I'm pretty confident Tom knows exactly what it does. It's not
exactly complicated and, really, it is *terrible*. Additionally, you
wouldn't need the casts if you were doing it the way Tom suggested and
it'd be about a billion times cleaner and faster. I would suggest you
read up on PostgreSQL arrays, how they work, and the operations
available on them, before attempting to do any more with them.

Thanks,

Stephen