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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

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

iD8DBQFHPPhvrzgMPqB3kigRAg8CAJ96YVaOO5TCBKkvqIYf1f pE5RNwEgCgjY8q
oQpdzR01ljbCF4/zkU0zAr8=
=xJ/R
-----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 :-)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster