SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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 |=---

  2. #2
    SitePoint Addict EvilDoppler's Avatar
    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

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    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.
    `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.
    - 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

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    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?

  5. #5
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  6. #6
    SitePoint Addict EvilDoppler's Avatar
    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*)

  7. #7
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  8. #8
    SitePoint Addict EvilDoppler's Avatar
    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 ????

  9. #9
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  10. #10
    SitePoint Addict EvilDoppler's Avatar
    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!!!!

  11. #11
    Perl/Mason Guru Flawless_koder's Avatar
    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';
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  12. #12
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy 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

  13. #13
    Perl/Mason Guru Flawless_koder's Avatar
    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 |=---

  14. #14
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking 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 pproduct_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
    PHP Code:
    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

  15. #15
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink WOAH!!

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

    oops

  16. #16
    SitePoint Enthusiast
    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 02:59.

  17. #17
    SitePoint Addict EvilDoppler's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •