473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Question about inserts

Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
6 1326

On 23/04/2004 11:36 mike wrote:
Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.

By blank, I assume you mean a zero-length string or the request parameter
being absent? I think you need to detect these conditions in your perl
program anf construct the SQL accordingly.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

On 23/04/2004 11:36 mike wrote:
Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.

By blank, I assume you mean a zero-length string or the request parameter
being absent? I think you need to detect these conditions in your perl
program anf construct the SQL accordingly.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
On Fri, Apr 23, 2004 at 11:36:51 +0100,
mike <mi**@redtux1.uklinux.net> wrote:
Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.


Ignore? If you want blanks turned into NULLs, probably the best place to
do this is in the application. I can think of three ways to do this:

Use NULLIF to replace the specified input strings into NULLs, while
leaving other strings as is.

Have the appkication detect the strings that should be NULL and replace them
with NULL (which shouldn't be quoted unlike the normal string data).

Split off the two columns that can have null values into their own tables
and only enter records there when the input is nonblank. This is perhaps
the closest to "ignoring" blanks that you can do.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4
On Fri, Apr 23, 2004 at 11:36:51 +0100,
mike <mi**@redtux1.uklinux.net> wrote:
Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.


Ignore? If you want blanks turned into NULLs, probably the best place to
do this is in the application. I can think of three ways to do this:

Use NULLIF to replace the specified input strings into NULLs, while
leaving other strings as is.

Have the appkication detect the strings that should be NULL and replace them
with NULL (which shouldn't be quoted unlike the normal string data).

Split off the two columns that can have null values into their own tables
and only enter records there when the input is nonblank. This is perhaps
the closest to "ignoring" blanks that you can do.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

mike <mi**@redtux1.uklinux.net> wrote:

Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.
You're not giving much information here. What do you mean by "blank
field?" That is to say: If the field (on the web form) is left blank,
what is your CGI script trying to do?

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.


One way would be not try to set table fields to invalid values ;).

Look into using placeholders in your prepared statements and
conditionally binding them to NULL if a web form field has been left
blank by the user.

--
Jim Seymour | Spammers sue anti-spammers:
js******@LinxNet.com | http://www.LinxNet.com/misc/spam/slapp.php
http://jimsun.LinxNet.com | Please donate to the SpamCon Legal Fund:
| http://www.spamcon.org/legalfund/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6

mike <mi**@redtux1.uklinux.net> wrote:

Is it possible to to the following

I have tables which are updated via webpage (perl) with fields of type
int and date (all nullable). If the values are blank the entire insert
fails, with a wrong type error.
You're not giving much information here. What do you mean by "blank
field?" That is to say: If the field (on the web form) is left blank,
what is your CGI script trying to do?

Is there any way to stop this behaviour so that the valid information is
allowed,but the blank values get ignored.


One way would be not try to set table fields to invalid values ;).

Look into using placeholders in your prepared statements and
conditionally binding them to NULL if a web form field has been left
blank by the user.

--
Jim Seymour | Spammers sue anti-spammers:
js******@LinxNet.com | http://www.LinxNet.com/misc/spam/slapp.php
http://jimsun.LinxNet.com | Please donate to the SpamCon Legal Fund:
| http://www.spamcon.org/legalfund/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: JP Belanger | last post by:
I have a question on select count(), which may betray my lack of database knowledge. Here goes. I have java code driving a transaction which goes like this: select count(*) from table ...
0
by: Clay Luther | last post by:
I am doing to large dataset performance tests with 7.3.4b2 today and I noticed an interesting phenomenon. My shared memory buffers are set at 128MB. Peak postmaster usage appears to be around 90MB....
25
by: Andreas Fromm | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Im building an user database with many tables keeping the data for the Address, Phone numbers, etc which are referenced by a table where I...
10
by: Patrick Olurotimi Ige | last post by:
I have a checkbox and i want to input Char "Y" or "N" to the Table In C# we could use for example :- ptrTest.Value = chkYN.Checked ? "Y" : "N"; Whats the equivalent in VB.NET?
5
by: Humble Geek | last post by:
Hi all. Quick and perhaps silly question, but... I am using Pg 7.3. I am writing a function using pgplsql. This function will perform multiple inserts. Let's say two of the inserts are as...
0
by: Emil Briggs | last post by:
I am working with an application whose performance is limited by the write activity (inserts, updates and deletes) on three specific tables. The database is RAM resident so read performance is not...
4
by: Michel Esber | last post by:
Hello, Environment: db2 V8 FP 13 LUW Our application currently uses: insert into table values ('A'),('B'),...('Z') We have used CLI arrays inserts (1000 array and commit size) and...
1
by: Amaxen1 | last post by:
Hello all, I have a general architecture question. We have a system that essentially does multiple processes/operations against a single table. The objective is to increase performance (i.e....
10
by: JohnO | last post by:
Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH...
24
by: Henry J. | last post by:
My app needs to insert thousand value rows into a mostly empty table (data are read from a file). I can either use inserts, or use merge. The advantage of using merge is that in the few cases...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.