Re: Inserting NULL into Integer column

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.

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;
}

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.

Anyway, the help on this list is much appreciated..

Jeremy

-----Original Message-----
From: scott.marlowe [mailto:scott(dot)marlowe(at)ihs(dot)com]
Sent: Wednesday, February 18, 2004 2:44 PM
To: Jeremy Smith
Cc: pgsql-sql(at)postgresql(dot)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.