Обсуждение: help
We used nullif('$value','') on inserts in mssql. We moved to postgres but the nullif() function doesn't match empty strings to each other to return null. MS SQL2000 nullif('1','') would insert 1 as integer even though wrapped in '' (aka string). Also nullif('','') would evaluate NULL (both equal returns NULL) and insert the "not a value" accordingly, not return text 'NULL' instead of return NULL if '' == ''. Postgresql will through an error since the defined return type is text. I would like to alter this function all together. How could I find it to manipulate it? We cannot always enter a value for a integer, text, boolean, etc column. Is there any other way to accomplish this in any language? C? Python? PL/x? If so can you suggest where we could learn how to do this or provide an example? Please help. Domo Matthew ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Very odd, what are you trying? kleptog=# select nullif('','') is null; ?column? ---------- t (1 row) kleptog=# select nullif('1','') is null; ?column? ---------- f (1 row) Works for me. Have a nice day, On Thu, Aug 25, 2005 at 01:44:52PM -0700, Matt A. wrote: > We used nullif('$value','') on inserts in mssql. We > moved to postgres but the nullif() function doesn't > match empty strings to each other to return null. > > MS SQL2000 nullif('1','') would insert 1 as integer > even though wrapped in '' (aka string). Also > nullif('','') would evaluate NULL (both equal returns > NULL) and insert the "not a value" accordingly, not > return text 'NULL' instead of return NULL if '' == ''. > Postgresql will through an error since the defined > return type is text. > > I would like to alter this function all together. How > could I find it to manipulate it? > > We cannot always enter a value for a integer, text, > boolean, etc column. > > Is there any other way to accomplish this in any > language? C? Python? PL/x? If so can you suggest where > we could learn how to do this or provide an example? > > Please help. > > Domo > Matthew > > > > ____________________________________________________ > Start your day with Yahoo! - make it your home page > http://www.yahoo.com/r/hs > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Please reply to the list also in the future, I'm going to sleep now. Anyway, it still works: kleptog=# create temp table x (a text); CREATE TABLE kleptog=# insert into x values (nullif('','')); INSERT 114760 1 kleptog=# insert into x values (nullif('1','')); INSERT 114761 1 kleptog=# select a,a is null as isnull from x; a | isnull ---+-------- | t 1 | f (2 rows) Please show us the error you got. Have a nice day, On Thu, Aug 25, 2005 at 02:57:29PM -0700, Matt A. wrote: > hi. I am speaking of INSERT not SELECT > > --- Martijn van Oosterhout <kleptog@svana.org> wrote: > > > Very odd, what are you trying? > > > > kleptog=# select nullif('','') is null; > > ?column? > > ---------- > > t > > (1 row) > > > > kleptog=# select nullif('1','') is null; > > ?column? > > ---------- > > f > > (1 row) > > > > Works for me. > > > > Have a nice day, > > -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Sorry to have replied directly to you. I assumed the reply address was that of the pgsql email.A thousand apologies. Anyway, I am new to postgres and looking to casts. I have wrote this question several times before and on this version I forgot to add the exact rules of why it's breaking and what i need it to do. I will try to be more clear... I use the rules to insert 0/1/null inserts into booleans, integers, text, etc. fields. Example: insert into table (bool_column) values nullif('','')); ERROR: column "bool_column" is of type boolean but expression is of type text. Where '' == '' should evaluate to NULL as OPAQUE (depreciated) or similiar return instead of TEXT cast. So if I could alter the cast from text to return either INTEGER or TEXT, that would super! I'm not sure if it's possible but if so I'm willing to do what it takes to accomplish it. I'm on a test box to see if postgresql will be suitable for our production environment (from MSSQL). So I can route around and tear things apart at will. Even changing nullif() to return 1/0/null for boolean and integer fields would be sufficient. It's hardly used on TEXT casts. Thanks for your time. I hope that I may have been more helpful on this reply. Thank you, Matthew --- Martijn van Oosterhout <kleptog@svana.org> wrote: > Please reply to the list also in the future, I'm > going to sleep now. > Anyway, it still works: > > kleptog=# create temp table x (a text); > CREATE TABLE > kleptog=# insert into x values (nullif('','')); > INSERT 114760 1 > kleptog=# insert into x values (nullif('1','')); > INSERT 114761 1 > kleptog=# select a,a is null as isnull from x; > a | isnull > ---+-------- > | t > 1 | f > (2 rows) > > Please show us the error you got. > > Have a nice day, > > > On Thu, Aug 25, 2005 at 02:57:29PM -0700, Matt A. > wrote: > > hi. I am speaking of INSERT not SELECT > > > > --- Martijn van Oosterhout <kleptog@svana.org> > wrote: > > > > > Very odd, what are you trying? > > > > > > kleptog=# select nullif('','') is null; > > > ?column? > > > ---------- > > > t > > > (1 row) > > > > > > kleptog=# select nullif('1','') is null; > > > ?column? > > > ---------- > > > f > > > (1 row) > > > > > > Works for me. > > > > > > Have a nice day, > > > > > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% > perspiration. A patent is a > > tool for doing 5% of the work and then sitting > around waiting for someone > > else to do the other 95% so you can sue them. > __________________________________ Yahoo! Mail Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html
On Thu, Aug 25, 2005 at 04:10:27PM -0700, Matt A. wrote: > Anyway, I am new to postgres and looking to casts. I > have wrote this question several times before and on > this version I forgot to add the exact rules of why > it's breaking and what i need it to do. I will try to > be more clear... > > I use the rules to insert 0/1/null inserts into > booleans, integers, text, etc. fields. > > Example: insert into table (bool_column) values > nullif('','')); > ERROR: column "bool_column" is of type boolean but > expression is of type text. First rule, the type of a functions is determined by its arguments. Neither of the arguments in your example are boolean so it doesn't know you want a boolean. nullif itself works on any type. kleptog=# create temp table x (t text, b bool, i integer); CREATE TABLE kleptog=# insert into x values (nullif('1','1'), nullif(true,true), nullif(4,4) ); INSERT 114774 1 kleptog=# select * from x; t | b | i ---+---+--- | | (1 row) All nulls... Your choice are to indicate in the arguments what type you want. In this case the arguments arn't booleans so that won't work. Your other option is to cast the result, which you can't because text -> bool is not a valid typecast. > > Where '' == '' should evaluate to NULL as OPAQUE > (depreciated) or similiar return instead of TEXT cast. > > So if I could alter the cast from text to return > either INTEGER or TEXT, that would super! I'm not sure > if it's possible but if so I'm willing to do what it > takes to accomplish it. Functions don't return OPAQUE, they can't because a function knows exactly what it's returning. In your case it's returning TEXT because that's what the arguments default to if it doesn't know any better. Technically, nullif takes arguments (anyelement,anyelement) which means it can take any type, as long as they're the same. Hope this clarifies it for you. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Yes. I know that using '' defines the field as a string so I get the TEXT cast. Let me try to show you why I need it in ''. I default my parameters in my application to empty strings. They don't need to be casted so I can override them at any time with an integer value, string, boolean, float, etc. So I typically will defalut value = '' value = '' nullif($value,'') // evaluates nullif(,''). ERROR. // override value now with integer value = 4 nullif('$value','') // evaluates nullif('4',''). Now we add 4 to int column // override again value = '' nullif('$value','') // evals to nullif('',''). Now we know it's NULL so let's insert NULL In MS SQL (no m$ - you like how they patented XML & tried patenting IPv6? Oh and tabbed browsing. Patents are a joke and hinder innovation) evaluats nullif('4','') as an INT 4 or nullif('','') returns any NULL -- no matter the column inserted into. Sorry if I'm being difficult or pestering. I just want to use postgres for my applications database and know I can get this working. All I need is integer columns evaluting in a nullif('1','') returns int = 1 nullif('','') returns int = null nullif('0','') returns int = 0 fashion upon inserting to an INTEGER column. Forget booleans. Forget text. Just integers. Is this possible? Nothing is impossible. Tell me it can be done! Could I use a C++ function to do what I'm trying to accomplish? Or is there an more elegant solution? (like a built in sql one) Thanks --- Martijn van Oosterhout <kleptog@svana.org> wrote: > On Thu, Aug 25, 2005 at 04:10:27PM -0700, Matt A. > wrote: > > Anyway, I am new to postgres and looking to casts. > I > > have wrote this question several times before and > on > > this version I forgot to add the exact rules of > why > > it's breaking and what i need it to do. I will try > to > > be more clear... > > > > I use the rules to insert 0/1/null inserts into > > booleans, integers, text, etc. fields. > > > > Example: insert into table (bool_column) values > > nullif('','')); > > ERROR: column "bool_column" is of type boolean but > > > expression is of type text. > > First rule, the type of a functions is determined by > its arguments. > Neither of the arguments in your example are boolean > so it doesn't know > you want a boolean. nullif itself works on any type. > > kleptog=# create temp table x (t text, b bool, i > integer); > CREATE TABLE > kleptog=# insert into x values (nullif('1','1'), > nullif(true,true), > nullif(4,4) ); > INSERT 114774 1 > kleptog=# select * from x; > t | b | i > ---+---+--- > | | > (1 row) > > All nulls... > > Your choice are to indicate in the arguments what > type you want. In > this case the arguments arn't booleans so that won't > work. Your other > option is to cast the result, which you can't > because text -> bool is > not a valid typecast. > > > > > Where '' == '' should evaluate to NULL as OPAQUE > > (depreciated) or similiar return instead of TEXT > cast. > > > > So if I could alter the cast from text to return > > either INTEGER or TEXT, that would super! I'm not > sure > > if it's possible but if so I'm willing to do what > it > > takes to accomplish it. > > Functions don't return OPAQUE, they can't because a > function knows > exactly what it's returning. In your case it's > returning TEXT because > that's what the arguments default to if it doesn't > know any better. > > Technically, nullif takes arguments > (anyelement,anyelement) which means > it can take any type, as long as they're the same. > > Hope this clarifies it for you. > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% > perspiration. A patent is a > > tool for doing 5% of the work and then sitting > around waiting for someone > > else to do the other 95% so you can sue them. > ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
On Fri, Aug 26, 2005 at 01:41:12 -0700, > All I need is integer columns evaluting in a > > nullif('1','') returns int = 1 > nullif('','') returns int = null > nullif('0','') returns int = 0 > > fashion upon inserting to an INTEGER column. Forget > booleans. Forget text. Just integers. Is this > possible? Nothing is impossible. Tell me it can be > done! Casting the parameters won't work since '' isn't a valid representation of an integer. But it looks like casting the output to int works. (It seemed to work in 8.0.3.)