Thread: 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.)