Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about inserts

Question about inserts

From
mike
Date:
Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.

Re: Question about inserts

From
"Stijn Vanroye"
Date:
Maybe you can do something with this function:

[quote from the postgres docs]
9.12.2. COALESCE
COALESCE(value [, ...])
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are
null.This is often useful to substitute a default value for null values when data is retrieved for display, for
example: 

SELECT COALESCE(description, short_description, '(none)') ...

Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is,
argumentsto the right of the first non-null argument are not evaluated.  
[end quote]

Isn't it possible to pass "NULL" to the query in stead of ''?
eg. update table set datefield=NULL

Regards,

Stijn Vanroye

> Is it possible to to the following
>
> I have tables which are updated via webpage (perl) with fields of type
> int and date (all nullable). If the values are blank the entire insert
> fails, with a wrong type error.
>
> Is there any way to stop this behaviour so that the valid
> information is
> allowed,but the blank values get ignored.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: Question about inserts

From
Paul Thomas
Date:
On 23/04/2004 11:36 mike wrote:
> Is it possible to to the following
>
> I have tables which are updated via webpage (perl) with fields of type
> int and date (all nullable). If the values are blank the entire insert
> fails, with a wrong type error.
>
> Is there any way to stop this behaviour so that the valid information is
> allowed,but the blank values get ignored.


By blank, I assume you mean a zero-length string or the request parameter
being absent? I think you need to detect these conditions in your perl
program anf construct the SQL accordingly.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business             |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: Question about inserts

From
Bruno Wolff III
Date:
On Fri, Apr 23, 2004 at 13:07:43 +0200,
  Stijn Vanroye <s.vanroye@farcourier.com> wrote:
> Maybe you can do something with this function:
>
> [quote from the postgres docs]
> 9.12.2. COALESCE
> COALESCE(value [, ...])

That's the inverse of what he would want if he used that approach. NULLIF
is what turns things into NULLs.

Re: Question about inserts

From
"Stijn Vanroye"
Date:
Bruno Wolff III wrote:
> On Fri, Apr 23, 2004 at 13:07:43 +0200,
>   Stijn Vanroye <s.vanroye@farcourier.com> wrote:
> > Maybe you can do something with this function:
> >
> > [quote from the postgres docs]
> > 9.12.2. COALESCE
> > COALESCE(value [, ...])
>
> That's the inverse of what he would want if he used that
> approach. NULLIF
> is what turns things into NULLs.
>
Indeed. I stand corrected. I use the two in my query, guess I just mixed them up :-)

My apologies.


Stijn Vanroye

Re: Question about inserts

From
Bruno Wolff III
Date:
On Fri, Apr 23, 2004 at 11:36:51 +0100,
  mike <mike@redtux1.uklinux.net> wrote:
> Is it possible to to the following
>
> I have tables which are updated via webpage (perl) with fields of type
> int and date (all nullable). If the values are blank the entire insert
> fails, with a wrong type error.
>
> Is there any way to stop this behaviour so that the valid information is
> allowed,but the blank values get ignored.

Ignore? If you want blanks turned into NULLs, probably the best place to
do this is in the application. I can think of three ways to do this:

Use NULLIF to replace the specified input strings into NULLs, while
leaving other strings as is.

Have the appkication detect the strings that should be NULL and replace them
with NULL (which shouldn't be quoted unlike the normal string data).

Split off the two columns that can have null values into their own tables
and only enter records there when the input is nonblank. This is perhaps
the closest to "ignoring" blanks that you can do.

Re: Question about inserts

From
jseymour@LinxNet.com (Jim Seymour)
Date:
mike <mike@redtux1.uklinux.net> wrote:
>
> Is it possible to to the following
>
> I have tables which are updated via webpage (perl) with fields of type
> int and date (all nullable). If the values are blank the entire insert
> fails, with a wrong type error.

You're not giving much information here.  What do you mean by "blank
field?"  That is to say: If the field (on the web form) is left blank,
what is your CGI script trying to do?

>
> Is there any way to stop this behaviour so that the valid information is
> allowed,but the blank values get ignored.

One way would be not try to set table fields to invalid values ;).

Look into using placeholders in your prepared statements and
conditionally binding them to NULL if a web form field has been left
blank by the user.

--
Jim Seymour                | Spammers sue anti-spammers:
jseymour@LinxNet.com       |     http://www.LinxNet.com/misc/spam/slapp.php
http://jimsun.LinxNet.com  | Please donate to the SpamCon Legal Fund:
                           |     http://www.spamcon.org/legalfund/