SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
-
May 10, 2002, 13:49 #1
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Postgres NULL reference operator no longer works?! (7.1 -> 7.2)
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---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 12, 2002, 00:26 #2
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks Flaw!!
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
-
May 12, 2002, 01:21 #3
- Join Date
- Jul 2001
- Location
- Missouri
- Posts
- 3,428
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Re: Postgres NULL reference operator no longer works?! (7.1 -> 7.2)
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.- Matt** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
"Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR
-
May 12, 2002, 01:34 #4
- Join Date
- Sep 2001
- Location
- Singapore
- Posts
- 5,269
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
May 13, 2002, 01:59 #5
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 13, 2002, 02:06 #6
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Darn!!
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*)
-
May 13, 2002, 02:13 #7
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 13, 2002, 02:29 #8
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Don't you always ?
Uhmmm..don't you always just post to yourself ????
-
May 13, 2002, 02:37 #9
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Right - that's it... no more help for YOU
Flawless---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 13, 2002, 02:47 #10
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Muwhahahaha
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!!!!
-
May 13, 2002, 02:55 #11
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
Code: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';
Hope you can pull this off without me having to write it for you
Let me know if you need more mate.
Flawless---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 13, 2002, 03:05 #12
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Dont bet on it!!
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
-
May 13, 2002, 03:15 #13
- Join Date
- Feb 2002
- Location
- Gatwick, UK
- Posts
- 1,206
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?---=| If you're going to buy a pet - get a Shetland Giraffe |=---
-
May 13, 2002, 03:25 #14
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yup with you all the way ol' Teadrinker!
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
PHP Code: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
i'll show it again
PHP Code:order by substring(p.name from 1 for position('.' in p.name)-1)::text::integer
-
May 13, 2002, 03:28 #15
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
WOAH!!
seems like the pretty [ PHP ] thingiemajiggie didn't like LONG scripts/sql.... LOL
oops
-
May 17, 2002, 03:55 #16
- Join Date
- Feb 2002
- Location
- Birmingham, UK
- Posts
- 86
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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>Last edited by mulletboy2; May 17, 2002 at 03:59.
-
May 18, 2002, 06:19 #17
- Join Date
- Dec 2001
- Location
- Perth, Australia
- Posts
- 217
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
tit ?
can everybody else feel the luv in this forum too ??
Bookmarks