pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInserting NULL into Integer column
10+ messages / 3 participants
[nested] [flat]
* Inserting NULL into Integer column
@ 2004-02-18 19:25 Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeremy Smith @ 2004-02-18 19:25 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks,
Jeremy
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
@ 2004-02-18 19:43 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
0 siblings, 1 reply; 10+ messages in thread
From: scott.marlowe @ 2004-02-18 19:43 UTC (permalink / raw)
To: Jeremy Smith <jer@highboard.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 20:15 ` Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeremy Smith @ 2004-02-18 20:15 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-sql
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.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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
@ 2004-02-18 20:19 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:43 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:53 ` Re: Inserting NULL into Integer column Rod Taylor <pg@rbt.ca>
0 siblings, 2 replies; 10+ messages in thread
From: scott.marlowe @ 2004-02-18 20:19 UTC (permalink / raw)
To: Jeremy Smith <jer@highboard.com>; +Cc: pgsql-sql
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.
>
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 20:43 ` Jeremy Smith <jer@highboard.com>
2004-02-18 20:44 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jeremy Smith @ 2004-02-18 20:43 UTC (permalink / raw)
To: pgsql-sql
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]."'";
}
}
Wow, great idea. I will definitely do this, thanks alot.
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.
No doubt that this is true. Of course even without the better code and
error checking, the extra features like stored procedures and automatic row
locking was more than enough to make the switch worth it.
Thanks again!
Jeremy
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:43 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
@ 2004-02-18 20:44 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 10+ messages in thread
From: scott.marlowe @ 2004-02-18 20:44 UTC (permalink / raw)
To: Jeremy Smith <jer@highboard.com>; +Cc: pgsql-sql
On Wed, 18 Feb 2004, Jeremy Smith wrote:
>
>
> 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]."'";
> }
> }
>
> Wow, great idea. I will definitely do this, thanks alot.
>
>
>
> 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.
>
>
> No doubt that this is true. Of course even without the better code and
> error checking, the extra features like stored procedures and automatic row
> locking was more than enough to make the switch worth it.
>
> Thanks again!
You're welcome! Enjoy getting to know Postgresql and all the great folks
on the lists, I know I have.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 20:53 ` Rod Taylor <pg@rbt.ca>
2004-02-18 20:58 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2004-02-18 20:53 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@ihs.com>; +Cc: Jeremy Smith <jer@highboard.com>; pgsql-sql
> and then you can foreach across the input:
>
> foreach($fields as $f){
> if (!$_POST[$f]){
> $_POST[$f]='DEFAULT';
> } else {
> $_POST[$f] = "'".$_POST[$f]."'";
> }
> }
Default in quotes isn't going to work, and please tell me you escape
those things with pg_escape_string() at some point.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:53 ` Re: Inserting NULL into Integer column Rod Taylor <pg@rbt.ca>
@ 2004-02-18 20:58 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 21:29 ` Re: Inserting NULL into Integer column Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 10+ messages in thread
From: scott.marlowe @ 2004-02-18 20:58 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Jeremy Smith <jer@highboard.com>; pgsql-sql
On Wed, 18 Feb 2004, Rod Taylor wrote:
> > and then you can foreach across the input:
> >
> > foreach($fields as $f){
> > if (!$_POST[$f]){
> > $_POST[$f]='DEFAULT';
> > } else {
> > $_POST[$f] = "'".$_POST[$f]."'";
> > }
> > }
>
> Default in quotes isn't going to work, and please tell me you escape
> those things with pg_escape_string() at some point.
Note that the ' marks aren't part of the string, they are the delimiter of
the string, and I always run every server with magic_quotes_gpc on.
anything else? :-)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:53 ` Re: Inserting NULL into Integer column Rod Taylor <pg@rbt.ca>
2004-02-18 20:58 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-18 21:29 ` Rod Taylor <pg@rbt.ca>
2004-02-18 21:50 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rod Taylor @ 2004-02-18 21:29 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@ihs.com>; +Cc: Jeremy Smith <jer@highboard.com>; pgsql-sql
> Note that the ' marks aren't part of the string, they are the delimiter of
> the string, and I always run every server with magic_quotes_gpc on.
>
> anything else? :-)
Good point. I looked at the single quotes of the second line and somehow
the DEFAULT got quoted as well ;)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Inserting NULL into Integer column
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Re: Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:53 ` Re: Inserting NULL into Integer column Rod Taylor <pg@rbt.ca>
2004-02-18 20:58 ` Re: Inserting NULL into Integer column scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 21:29 ` Re: Inserting NULL into Integer column Rod Taylor <pg@rbt.ca>
@ 2004-02-18 21:50 ` scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 0 replies; 10+ messages in thread
From: scott.marlowe @ 2004-02-18 21:50 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: Jeremy Smith <jer@highboard.com>; pgsql-sql
On Wed, 18 Feb 2004, Rod Taylor wrote:
> > Note that the ' marks aren't part of the string, they are the delimiter of
> > the string, and I always run every server with magic_quotes_gpc on.
> >
> > anything else? :-)
>
> Good point. I looked at the single quotes of the second line and somehow
> the DEFAULT got quoted as well ;)
Oh, and I'm stuck using add_slashes (or the magic_quotes_gpc thingie)
'cause I'm on a server that's being eoled in favor of .net, and it's
running PHP 4.0.6... ugh. We really gotta get it upgraded soon.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2004-02-18 21:50 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-18 19:25 Inserting NULL into Integer column Jeremy Smith <jer@highboard.com>
2004-02-18 19:43 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:15 ` Jeremy Smith <jer@highboard.com>
2004-02-18 20:19 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:43 ` Jeremy Smith <jer@highboard.com>
2004-02-18 20:44 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 20:53 ` Rod Taylor <pg@rbt.ca>
2004-02-18 20:58 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-18 21:29 ` Rod Taylor <pg@rbt.ca>
2004-02-18 21:50 ` scott.marlowe <scott.marlowe@ihs.com>
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