Thread: 8.3 vs 8.2 sql compatiblity issue
Hi, Just running some queries that have worked from 7.4 through 8.2 and they don't seem to work on 8.3. select case when a.attnum = any(conkey) then true else false end from pg_constraint where contype = 'p' and conrelid = c.oid This one is puking on a.attnum = any(conkey) returns the following error: SQL State: 42883 ERROR: operator does not exist: smallint = text HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. 0 Record(s) Returned -------------------------------------------------------------------- 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 i.e. attnum = pi.indkey[0]) used to work but fails in 8.3 Ideas? Thanks, Tony
-----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-----
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
Attachment
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 :-)