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
Date: Wed, 18 Feb 2004 12:43:34 -0700 (MST)
Message-ID: <Pine.LNX.4.33.0402181239030.2832-100000@css120.ihs.com> (raw)
In-Reply-To: <FKEOIJEHOIKIOEHCGNCLEEMEEDAA.jer@highboard.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scott.marlowe@ihs.com, jer@highboard.com
Subject: Re: Inserting NULL into Integer column
In-Reply-To: <Pine.LNX.4.33.0402181239030.2832-100000@css120.ihs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox