Date: Wed, 18 Feb 2004 13:19:48 -0700 (MST)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Jeremy Smith <jer@highboard.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Inserting NULL into Integer column
In-Reply-To: <FKEOIJEHOIKIOEHCGNCLGEMFEDAA.jer@highboard.com>
Message-ID: <Pine.LNX.4.33.0402181317100.3215-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 18 Feb 2004, Jeremy Smith wrote:

> Scott,
> 
> I understand that MySQL's adherence to the standards must be lazy as I am
> running into frequent issues as I transfer my site.  Unfortunately I have
> over 2500 queries, and many more of them needed to be rewritten than I ever
> would have imagined.  I guess MySQL is the IE of open source DB, and
> PostgreSQL is Netscape / Mozilla, in more ways than one.

Good comparison.

> I guess in some sense, since I relied on MySQL's laziness, my code also
> became a bit lazy.  There are many locations where I accept user input from
> a form, and then have a process page.  And on that process page I might have
> hundreds of variables that look like:
> 
> $input = $_POST['input'];
> 
> and in the old days, if that was an empty value and inserted into a mysql
> query, it would just revert to the default.  Now it looks like I need to:
> 
> $input = $_POST['input'];
> if (!$input) {
>     $input = DEFAULT;
> }

I've run into this kind of thing before.  IT helps if you have an array of 
all your fields like:

$fields = array("field1","field3","last_name");

and then you can foreach across the input:

foreach($fields as $f){
  if (!$_POST[$f]){
    $_POST[$f]='DEFAULT';
  } else {
    $_POST[$f] = "'".$_POST[$f]."'";
  }
}

> over and over and over and over.... :)  I guess I am just looking for a
> shortcut since the site conversion has already taken a week and counting,
> when I originally was misguided enough to think it would take hours.

Well, you might find yourself rewriting fair portions of your site, but 
usually you wind up with better code and better checking, so it's a bit of 
a trade off.

> Anyway, the help on this list is much appreciated..
> 
> Jeremy
> 
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, February 18, 2004 2:44 PM
> To: Jeremy Smith
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Inserting NULL into Integer column
> 
> 
> On Wed, 18 Feb 2004, Jeremy Smith wrote:
> 
> > Hi,
> >
> > in mysql I was able to make an insert such as:
> >
> > INSERT INTO TABLE (integervariable) VALUES ('')
> >
> > and have it either insert that variable, or insert the default if it had
> > been assigned.  In postgresql it gives and error every time that this is
> > attempted.  Since I have so many queries that do this on my site already,
> is
> > there any way to set up a table so that it just accepts this sort of
> query?
> 
> First off, the reason for this problem is that Postgresql adheres to the
> SQL standard while MySQL heads off on their own, making it up as they go
> along.  This causes many problems for people migrating from MySQL to
> almost ANY database.
> 
> Phew, now that that's out of the way, here's the standard ways of doing
> it.
> 
> Use DEFAULT:  If no default is it will insert a NULL, otherwise the
> default will be inserted:
> insert into table (integervar) values (DEFAULT);
> 
> OR
> 
> Leave it out of the list of vars to be inserted
> insert into table (othervars, othervars2) values ('abc',123);
> 
> OR
> 
> Insert a NULL if that's what you want:
> 
> insert into table (integervar) values (NULL);
> 
> Note that NULL and DEFAULT are not quoted.
> 
> 
> 
>