Postgres NULL reference operator no longer works?! (7.1 -> 7.2)

[Flawless_koder]

I just upgraded postgres from 7.1.x to 7.2.x

I was foolish enough to use the = operator to call
NULL value records from the database, ie:

select genre from genres where parent = NULL;

This no longer works, the operator must now be IS,
and this now works.

Has anyone else found this, and are there any other
common pitfalls i might walk into that someone else
has encountered?

Flawless

[EvilDoppler]

We're considering upgrading to 7.2 so that piece of advice came at a good time..

thanks for that!

BTW...have you used pgadmin II before ? For some reason we cannot make it run..can install it with no problems at all but when we run it the "splash" screen flashes 1 pico second and that's it. Been looking online for bug reports but haven't found anything out there. Got any tips on it, let me know!

Cheeers

[DR_LaRRY_PEpPeR]

Originally posted by Flawless_koder
select genre from genres where parent = NULL;

This no longer works, the operator must now be IS,
and this now works.

`WHERE col=NULL' shouldn't be allowed. it should be `WHERE col IS NULL'. since NULL is a "unknown value," using `col=NULL' is testing whether an unknown value is equal to an unknown value. it shouldn't be possible.

[redemption]

yeah i also thought that it was weird that PostgreSQL 7.1 allowed testing of NULL like that... flawless_koder it really worked like that in the past?

[Flawless_koder]

Yep - it really did.

I took it as a surprise at first - but once i got used to it i started to like it.
Now i've had to regex all my Mason

Nice to see you on here Dopples! I don't use anything but the psql console, sorry mate.

Flawless

[EvilDoppler]

Yeah, PostgreSQL 7.1 does actually allow the = NULL query (oddly enough) but as Flaw pointed out that once you get used to it then it's actually nice that you don't have to "think outside the box" for NULL value queries.



Maybe it's just me being a slacker...dunno...

Flaw => yeah, thought i'd pop in and throw a bit of useless knowledge around (gotta keep my pathetic post amount up..*cough cough*)

[Flawless_koder]

I suppose that cough cough is at me then

Well you know me - i've got a fair bit to say
every now and then in the Javascript / DHTML
forum and not to mention the Mason one.

Shame there isn't anything for python / COBOL,
but hey - i'd be posting to myself anyway

Flawless

[EvilDoppler]

Uhmmm..don't you always just post to yourself ????

[Flawless_koder]

Right - that's it... no more help for YOU

Flawless

[EvilDoppler]

Slow poke!! he hehe

ok... php/postgresql question (well more postgresql than php)

I need a postgresql function or something which can sort a by numerical output when you have a naming convention like this\

1. Teadrinker
9. Comic Books
10. Fruitloops

Currently it will display 10. Fruitloops before 1. Teadrinker because 10. is regarded less than 1. with a alphabetical sort.

Gimme solution..now!!!!

[Flawless_koder]

Well - as you might have seen in a long discussion i had on here previously, postgres supports three types of function (store procedure in one case)

SQL stored function
Procedural language function (stored and compiled - Perl/PostgreSQL)
Programming language function (external, compiled - C normally)

For you the best of these is probably plpgsql - the Procedural compiled function.

To create the language plpgsql go to the postgres console ( CMD: psql database postgres )
and enter:

CREATE FUNCTION plpgsql_call_handler() RETURNS OPAQUE AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';

where '/usr/lib/pgsql/plpgsql.so' is a variable location ( use CMD: locate plpgsql.so on linux )

then enter:

CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

This will give you the ability to use perl/pg functions with logic. This might be something you're already doing, but just in case you're not.

The function takes a DECLARE BEGIN END format:
(example from a triggered procedure)

CREATE FUNCTION Func_bids_items() RETURNS opaque AS '
 DECLARE
  	item_id 	int;
  	bid 		numeric(10,2);
        current_bid	numeric(10,2);
	bid_count	int;
 BEGIN
	SELECT INTO item_id, bid item_id, bid FROM bids WHERE id=NEW.id;
	SELECT INTO current_bid current FROM items where id = item_id;
	SELECT INTO bid_count bids FROM items where id = item_id;

	if (current_bid < bid){
		UPDATE items SET current = bid WHERE id = item_id;
		UPDATE items SET bids = (bid_count+1) WHERE id = item_id;
		}
	else {
		DELETE FROM bids WHERE id = NEW.id;
		}
END;'
LANGUAGE 'plpgsql';

There's plenty of info on how to use functions and a huge chapter in postgres manuals (and the O'Reilly Practical Postgres) on how to use PL/pgSQL.

Hope you can pull this off without me having to write it for you

Let me know if you need more mate.

Flawless

[EvilDoppler]

Urrgghh....never done that before but might have to do it...****e...

ok...i'll work on that a bit and see what i can knit together...might be the only way to go..


uhmm btw..did i forget to mention that the naming convention allows for more than 1 with the same name..

so there can be multiple

10. Fruitloops
10. Fruitloops

but the difference could be size, colour, weight, price...etc etc...

I'm stuck with this big time...it's pissing me off cause i originally got the array with the results in to work perfectly..but suddenly 2 more of the 10. fruitloops pops up and i have to rewrite the entire script unless i can find a suitable solution.

Cheers mate and thanks for the advice..might have to come crawling to you eventually..will atttempt this ****e first myself though.

Cheers Cheers Cheers

[Flawless_koder]

You're not telling me that the field has actually:
10. Fruitloops
in it?!!!?!

Shouldn't it have an id which is autoincrementing (a serial type in Pg) and that would contain the 10?

Otherwise you're going to have to do something like create a split on the "." in a pgsql function - and then create a temporary view/table and then sort ascending....

You with me so far?

[EvilDoppler]

Yeah...i'm telling you that the field actually contain the number AND the product name...

Freaky client decided to do the function the cheap way (other way would have cost them some more in the backend) and we had to find a solution for it...this works...look carefully and see the smartness of my developer

select p.* from products as p, product_category as c where p.prod_id=c.prod_id and c.category_id='48' order by substring(p.name from 1 for position('.' in p.name)-1)::text::integer 

notice the order by ???

i'll show it again

order by substring(p.name from 1 for position('.' in p.name)-1)::text::integer 

that works now..typecasting the value in the substring before the . is what made the difference

[EvilDoppler]

seems like the pretty [ PHP ] thingiemajiggie didn't like LONG scripts/sql.... LOL

oops

[mulletboy2]

dopp - sql server supports patindex and charindex... if you were to get the chars before the first fullstop, cast to an int, then order by that it should work.. assuming postgres supports something similar. IMHO, it would be better to check the value on the insert, and append a new field to the table called OrderBy or something, which contains that int value... it would be much quicker for lookup, and only slow down the insert slightly.

best of luck,

mullie

<edit> mullie you tit, read all of the replies before replying! Still, my other suggestion is a better approach for most circumstances, i would think. It doesn't take much to append a field to a table, then run a query for all records in the table (update table set OrderBy_int_field = [whatever your pgsql was]), and then do it in the future upon insert of new records</edit>

[EvilDoppler]

can everybody else feel the luv in this forum too ??