Date: Wed, 18 Feb 2004 12:43:34 -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: <FKEOIJEHOIKIOEHCGNCLEEMEEDAA.jer@highboard.com>
Message-ID: <Pine.LNX.4.33.0402181239030.2832-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.