[SQL] postgresql function not accepting null values in select statement
Hi, If I pass null value as the parameter of postgresql function, which is used in the where clause of select statement is not functioning properly. I have also changed the value of transform_null_equals = on in the conf file and restarted postgresql. But it is still giving error. Is there any possible solution. Thanks, Jyoti
Re: [SQL] postgresql function not accepting null values in select statement
Jyoti Seth wrote: If I pass null value as the parameter of postgresql function, which is used in the where clause of select statement is not functioning properly. Either: 1. You're talking about frooble(), in which case it's supposed to do that. or 2. You'll need to tell us what function it is, how you're using it and what you think should happen. My guess is that you're getting a null as the result and that's not doing what you'd expect in your where clause. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] postgresql function not accepting null values in select statement
Hi, I have a the following procedure CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) RETURNS SETOF t_functionaries AS $BODY$ DECLARE rec t_functionaries%ROWTYPE; begin FOR rec IN SELECT f.functionaryid, f.category,f.description FROM functionaries f where f.statecd=p_statecd LOOP return next rec; END LOOP; return; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; In the functionaries table statecd is a null field. When I pass some integer value to the above procedure it works correctly but if I pass null value in p_statecd it doesn't show anything whereas it has values and if I write the select statement separately it gives values Thanks, Jyoti -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Friday, February 22, 2008 2:35 PM To: Jyoti Seth Cc: [email protected] Subject: Re: [SQL] postgresql function not accepting null values in select statement Jyoti Seth wrote: > > If I pass null value as the parameter of postgresql function, which is used > in the where clause of select statement is not functioning properly. Either: 1. You're talking about frooble(), in which case it's supposed to do that. or 2. You'll need to tell us what function it is, how you're using it and what you think should happen. My guess is that you're getting a null as the result and that's not doing what you'd expect in your where clause. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] postgresql function not accepting null values inselect statement
Can you try this... CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) RETURNS SETOF t_functionaries AS $BODY$ DECLARE rec t_functionaries%ROWTYPE; BEGIN FOR rec IN ( SELECT f.functionaryid, f.category, f.description FROM functionaries f WHERE f.statecd IS NOT DISTINCT FROM p_statecd) LOOP return next rec; END LOOP; return; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; >>> "Jyoti Seth" <[EMAIL PROTECTED]> 2008-02-22 10:22 >>> Hi, I have a the following procedure CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) RETURNS SETOF t_functionaries AS $BODY$ DECLARE rec t_functionaries%ROWTYPE; begin FOR rec IN SELECT f.functionaryid, f.category,f.description FROM functionaries f where f.statecd=p_statecd LOOP return next rec; END LOOP; return; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; In the functionaries table statecd is a null field. When I pass some integer value to the above procedure it works correctly but if I pass null value in p_statecd it doesn't show anything whereas it has values and if I write the select statement separately it gives values Thanks, Jyoti -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Friday, February 22, 2008 2:35 PM To: Jyoti Seth Cc: [email protected] Subject: Re: [SQL] postgresql function not accepting null values in select statement Jyoti Seth wrote: > > If I pass null value as the parameter of postgresql function, which is used > in the where clause of select statement is not functioning properly. Either: 1. You're talking about frooble(), in which case it's supposed to do that. or 2. You'll need to tell us what function it is, how you're using it and what you think should happen. My guess is that you're getting a null as the result and that's not doing what you'd expect in your where clause. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] postgresql function not accepting null values in select statement
> > Hi, > > When you pass non-null values in p_statecd the result should work fine, > but when you pass NULL in p_statecd ... the equal operator stops to work as > you as expect it to. > > Please see this documentation: > http://www.postgresql.org/docs/8.2/interactive/functions-comparison.html > from where I quote: "Do *not* write *expression* = NULL because NULL is > not "equal to" NULL. (The null value represents an unknown value, and it > is not known whether two unknown values are equal.) This behavior conforms > to the SQL standard." > > As the document suggests you may want to try this way out: > > WHERE f.statecd IS NOT DISTINCT FROM p_statecd > > This would take care of both NULL and non-NULL values. > > *Robins* > > > -- Forwarded message -- > From: Jyoti Seth <[EMAIL PROTECTED]> > Date: Fri, Feb 22, 2008 at 2:52 PM > Subject: Re: [SQL] postgresql function not accepting null values in select > statement > To: Richard Huxton <[EMAIL PROTECTED]> > Cc: [email protected] > > > Hi, > > I have a the following procedure > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > RETURNS SETOF t_functionaries AS > $BODY$ > DECLARE rec t_functionaries%ROWTYPE; > begin > FOR rec IN >SELECT f.functionaryid, f.category,f.description >FROM functionaries f > where f.statecd=p_statecd > >LOOP >return next rec; >END LOOP; > return; > end; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > In the functionaries table statecd is a null field. When I pass some > integer > value to the above procedure it works correctly but if I pass null value > in > p_statecd it doesn't show anything whereas it has values and if I write > the > select statement separately it gives values > > Thanks, > Jyoti > > -Original Message- > From: Richard Huxton [mailto:[EMAIL PROTECTED] > Sent: Friday, February 22, 2008 2:35 PM > To: Jyoti Seth > Cc: [email protected] > Subject: Re: [SQL] postgresql function not accepting null values in select > statement > > Jyoti Seth wrote: > > > > If I pass null value as the parameter of postgresql function, which is > used > > in the where clause of select statement is not functioning properly. > > Either: > > 1. You're talking about frooble(), in which case it's supposed to do that. > > or > > 2. You'll need to tell us what function it is, how you're using it and > what you think should happen. > > My guess is that you're getting a null as the result and that's not > doing what you'd expect in your where clause. > > > -- > Richard Huxton > Archonet Ltd > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > >
Re: [SQL] postgresql function not accepting null values in select statement
Jyoti Seth wrote: Hi, I have a the following procedure CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) SELECT f.functionaryid, f.category,f.description FROM functionaries f where f.statecd=p_statecd In the functionaries table statecd is a null field. When I pass some integer value to the above procedure it works correctly but if I pass null value in p_statecd it doesn't show anything whereas it has values and if I write the select statement separately it gives values You can't be getting results from a query WHERE statecd = NULL, because NULL = NULL returns NULL, which is equivalent to false in a WHERE clause. If you want to check for NULL you need to use statecd IS NULL, but if you are treating it like a value then you're using it incorrectly. -- Richard Huxton Archonet Ltd ---(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
[SQL] Delete with foreign keys
I have three tables -- CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20)); CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id)); CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id)); I want to delete all place and data rows which reference specific names, but not the names themselves. I can do it like this: DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%')); DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'); but it seems rather roundabout, and I wonder whether the EXISTS and IN business is slow. Is there some way to do it using JOINs? I think of something like this: DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%'; but I don't want to delete the name rows. Then I think of this: DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%'; but I feel uneasy about the two separate name references when the table is not named in the FROM clause. Maybe that's just my novicity. I also wonder about getting fancy and ending up with SQL specific to a database; I don't have any plans to migrate, but I try to avoid branding my SQL. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] SQL standards in Mysql
Here's one Mysql developer's response to adding (fixing) the integer/bigint/tinyint types to their CAST function: http://bugs.mysql.com/bug.php?id=34562 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL standards in Mysql
Ken Johanson wrote: > Here's one Mysql developer's response to adding (fixing) the > integer/bigint/tinyint types to their CAST function: > > http://bugs.mysql.com/bug.php?id=34562 So they are anal too, but in the opposite direction? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] postgresql function not accepting null values inselect statement
On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > Can you try this... > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > RETURNS SETOF t_functionaries AS > $BODY$ > DECLARE > rec t_functionaries%ROWTYPE; > BEGIN > FOR rec IN ( > SELECT f.functionaryid, f.category, f.description > FROM functionaries f > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > LOOP > return next rec; > END LOOP; > return; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > a newbie question. Could you explain why yours works? I don't understand how it works if p_statecd = NULL -- John Fabiani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL standards in Mysql
On Fri, Feb 22, 2008 at 1:57 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Ken Johanson wrote: > > Here's one Mysql developer's response to adding (fixing) the > > integer/bigint/tinyint types to their CAST function: > > > > http://bugs.mysql.com/bug.php?id=34562 > > So they are anal too, but in the opposite direction? No, they're just hopelessly lost and making good time. Or lazy. Or both. I'm not sure. There's example after example of things in the mysql bug database that should make anyone considering it as a database engine cringe and walk away shaking their head. for instance, someone JUST submitted this bug: http://bugs.mysql.com/bug.php?id=34231 Which is about a problem with the mysql packages including a statically linked zlib which makes it impossible to compile php against it. Problem is, their inability to properly package / not package zlib with mysql is an ongoing problem. As far back as 2004 it showed up. Then was fixed, then showed up again, then was fixed. Here's the most recent "we fixed it! Oh shit it's broken again" bug record: http://bugs.mysql.com/bug.php?id=15255 Before that we had http://bugs.mysql.com/bug.php?id=3700 After that we had http://bugs.mysql.com/bug.php?id=28569 and now the latest reports. It's like the Keystone Kops create RPM packages reading those messages. Pick any subject area. Foreign keys, SQL compliance, and on and on and you'll find dozens of the same bugs coming and going as one person stomps it and another apparently reinstates it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SQL standards in Mysql
On 2008-02-22 16:13, Scott Marlowe wrote:
There's example after example of things in the mysql bug database that should
make anyone considering it as a database engine cringe and walk away shaking
their head.
I don't understand why anyone wanting a real SQL DB would pick MySQL.
Four years ago I knew nothing about SQL (I still pronounce it S-Q-L),
but wanted to set up a real DB with the maximum flexibility. So, I:
1. Asked a friend in the DB world (primarily Oracle) what he
recommended, and he said "MySQL".
2. I then bought a book on SQL ("Using SQL", by Rafe Colburn, ISBN
0-7897-1974-6, © 2000 Que Publishing), which mentions every common SQL
DB __except__ PostgreSQL.
So, I went with PostgreSQL. Why? From the book, it was clear that
MySQL lacked so many features of a decent SQL DB. In particular (at the
time) VIEWs and sub-selects. I didn't know much about VIEWs and
sub-selects, but it appeared to me that they were pretty
important/powerful features that any SQL DB should have. PostgreSQL was
the only one left standing (at least on my budget). (I ran it on a
Pentium 233 for a year before I upgraded the hardware.)
As of now, MySQL has VIEWs and sub-selects, but there appear to be a
number of other little "gotchas" that lurk (which the original poster of
this and the related threads has so amply illustrated). One of the
advantage of standards-compliant software is that, while you may be
surprised by some feature, that feature has been examined by a number of
people and (typically) found to be the best way of being consistent in a
broader view, rather than a feature that has been written (or not) for
the sake of expediency in a particular implementation. Further, many
standards are features that, while sometimes not met by existing
implementations, are at least a goal of consistency and functionality
that is aspired to (and usually planned for in future releases).
The sad fact is that we live in a world of expediency (not to mention a
bit of hype). Many people don't even do the elementary research that I
did before glibly picking a DB for their server, in their rush to be the
next dot-com (or other) success. As a result, PostgreSQL is not
supported by some software packages. For example, I think phpBB is the
only major message board software that supports PostgreSQL (see
http://www.phpbb.com/about/features/compare.php ), and in fact has for
some time. Of course, they have a DB abstraction layer (wow, what an
concept!), which allows them to easily support a number of DBs. Of
course, what does that tell you about the level of design and
professionalism of phpBB versus the others? Note that I have no
connection with the phpBB project, and in fact find many of the
developers arrogant. I'm just saying that any software that doesn't
support a number of DBs probably wasn't designed and/or implemented
properly.
Hopefully, some event in the near future will tip the scales in the
public perception of SQL DBs, and PostgreSQL will get better respect.
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] SQL standards in Mysql
On Fri, Feb 22, 2008 at 7:39 PM, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote: > So, I went with PostgreSQL. Why? From the book, it was clear that > MySQL lacked so many features of a decent SQL DB. In particular (at the > time) VIEWs and sub-selects. Note that unless someone's done some hacking recently, mysql uses nested loops for subselects. Which is fine for a few dozen or hundred entries. not so much for 1M rows. Handling subselects is as much about performance as it is about convenience. There was a time a few years ago when oracle would beat the pants off postgresql or mysql if you did this: delete from tablea where id in (select id from tableb); when tableb was pretty large. PostgreSQL hackers fixed that issue some time ago. MySQL, as far as I know, has no plans to fix their poor performance. Because somebody checked off subselect and that's all they needed, a check box. > the only one left standing (at least on my budget). (I ran it on a > Pentium 233 for a year before I upgraded the hardware.) Oh man, this takes me back. My first two postgresql servers were both pg v6.5.3 or so and ran on: Pentium-100 with 64 Meg of ram, Quad CPU Sparc-20 (4x50MHz CPUs) with a stack of SCSI hard drives running the last version of RH to support sparc back then. > supported by some software packages. For example, I think phpBB is the > only major message board software that supports PostgreSQL (see > http://www.phpbb.com/about/features/compare.php ), and in fact has for > some time. Of course, they have a DB abstraction layer (wow, what an > concept!), which allows them to easily support a number of DBs. Of > course, what does that tell you about the level of design and > professionalism of phpBB versus the others? Note that I have no > connection with the phpBB project, and in fact find many of the > developers arrogant. I'm just saying that any software that doesn't > support a number of DBs probably wasn't designed and/or implemented > properly. Well, somebody needs to teach them how this thing called patch works. The fact that all the hacks are installed by hand makes me queezy about messing with any modifications. Note that w-agora is a pretty nice bbs that supports mysql, oracle, and pgsql. I like the interface, but it doesn't look like there's a lot of recent development on it. I like the fact that it uses a nice threaded interface and can work with oracle, pgsql, or mysql. > Hopefully, some event in the near future will tip the scales in the > public perception of SQL DBs, and PostgreSQL will get better respect. I think it's slowly happening, not one big event, but a bunch of little experiences adding up. I think one of the things helping that in the long run is the number of very capable DBAs that are slowly coming to pgsql from other dbs, like DB2 or Oracle or Informix. ---(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
Re: [SQL] SQL standards in Mysql
On Fri, Feb 22, 2008 at 11:03 PM, Mail Delivery Subsystem <[EMAIL PROTECTED]> wrote: > This is an automatically generated Delivery Status Notification > > Delivery to the following recipient failed permanently: > > [EMAIL PROTECTED] > To: "Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> Look, if you're going to send email to this list from an address that doesn't accept email from anything but this list, and since this list by default is a "reply to all" list (i.e. people hit reply to all) the LEAST you can do is reconfigure your Mail client to change the reply to field to point to whatever list it is you are sending to. Or have the decency to program your MTA to just throw those messages away. Bouncing messages from a public list is kinda rude. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SQL standards in Mysql
Ken Johanson <[EMAIL PROTECTED]> writes: > Here's one Mysql developer's response to adding (fixing) the > integer/bigint/tinyint types to their CAST function: > http://bugs.mysql.com/bug.php?id=34562 Hmm ... while I'm certainly not someone to defend mysql on a regular basis, I can see their point of view here. They are supporting a limited (and clearly documented) subset of the SQL spec in this area, so it's surely a feature request rather than a bug that they don't support more. The disagreement seems to come down to what the priority of the feature addition ought to be. We have a few sore spots of our own on questions like this one, so I'm disinclined to throw the first stone ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Delete with foreign keys
[EMAIL PROTECTED] wrote: I have three tables -- CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20)); CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id)); CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id)); I want to delete all place and data rows which reference specific names, but not the names themselves. I can do it like this: DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%')); DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'); but it seems rather roundabout, and I wonder whether the EXISTS and IN business is slow. Is there some way to do it using JOINs? I think of something like this: DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%'; but I don't want to delete the name rows. Then I think of this: DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%'; but I feel uneasy about the two separate name references when the table is not named in the FROM clause. Maybe that's just my novicity. I also wonder about getting fancy and ending up with SQL specific to a database; I don't have any plans to migrate, but I try to avoid branding my SQL. Hello, I am Dipesh Mistry from Ahmedabad-India. I have the solution for you. First drop the constraint on table data. Example. alter table data drop constraint data_place_id_fkey; And in second step add new Constraint. Example. alter table data add constraint data_place_id_fkey FOREIGN KEY(place_id) REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE; Now when you test query like, delete from place where name_id in(select id from name where str='dip%'); So, you can try this above step. -- With Warm Regards, Dipesh Mistry Information Technology Dept. GaneshaSpeaks.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Delete with foreign keys
On Sat, Feb 23, 2008 at 12:42:13PM +0530, dipesh wrote: >alter table data add constraint data_place_id_fkey FOREIGN KEY(place_id) > REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE; Interestingly different way of doing it. Thanks. It makes me think too :-) -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
