Thread: Setting a field to default if blank value
Looking for the best way of handling this, would like to do it in the database and not in my apps. I set a field to 'Not Null', but if a blank value comes in, the field will accept it as just that, a blank string. How can avoid this using a trigger or function? I thought about a function that would check the field and then place a trigger, but that gets to be a lot of functions and triggers for different fields. Is there a way to pass a field name to the function? (in plpgsql) Or what is the best way to handle this for many different fields in many tables? -- Robert
If you mean blank like '', then if the field is a string, it would be normal behavior as is. If it's any other type of field, set a default value and it will take the place of a non existent value. Robert Fitzpatrick wrote: > Looking for the best way of handling this, would like to do it in the > database and not in my apps. I set a field to 'Not Null', but if a blank > value comes in, the field will accept it as just that, a blank string. > How can avoid this using a trigger or function? I thought about a > function that would check the field and then place a trigger, but that > gets to be a lot of functions and triggers for different fields. Is > there a way to pass a field name to the function? (in plpgsql) > > Or what is the best way to handle this for many different fields in many > tables? > > -- > Robert > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
> Robert Fitzpatrick wrote: > > Looking for the best way of handling this, would like to do > it in the > > database and not in my apps. I set a field to 'Not Null', but if a > > blank value comes in, the field will accept it as just > that, a blank > > string. How can avoid this using a trigger or function? I thought > > about a function that would check the field and then place > a trigger, > > but that gets to be a lot of functions and triggers for different > > fields. Is there a way to pass a field name to the function? (in > > plpgsql) > > > > Or what is the best way to handle this for many different fields in > > many tables? > If you mean blank like '', then if the field is a string, it > would be normal behavior as is. > Yes, they are strings (varchar). Know the best way to handle it? -- Robert
just put '' in the insert statement, it will be a blank field, no big deal. Robert Fitzpatrick wrote: >>Robert Fitzpatrick wrote: >> >>>Looking for the best way of handling this, would like to do >> >>it in the >> >>>database and not in my apps. I set a field to 'Not Null', but if a >>>blank value comes in, the field will accept it as just >> >>that, a blank >> >>>string. How can avoid this using a trigger or function? I thought >>>about a function that would check the field and then place >> >>a trigger, >> >>>but that gets to be a lot of functions and triggers for different >>>fields. Is there a way to pass a field name to the function? (in >>>plpgsql) >>> >>>Or what is the best way to handle this for many different fields in >>>many tables? > > >>If you mean blank like '', then if the field is a string, it >>would be normal behavior as is. >> > > > Yes, they are strings (varchar). Know the best way to handle it? > > -- > Robert > > >
On Mon, 28 Apr 2003, Robert Fitzpatrick wrote: > > Robert Fitzpatrick wrote: > > > Looking for the best way of handling this, would like to do > > it in the > > > database and not in my apps. I set a field to 'Not Null', but if a > > > blank value comes in, the field will accept it as just > > that, a blank > > > string. How can avoid this using a trigger or function? I thought > > > about a function that would check the field and then place > > a trigger, > > > but that gets to be a lot of functions and triggers for different > > > fields. Is there a way to pass a field name to the function? (in > > > plpgsql) > > > > > > Or what is the best way to handle this for many different fields in > > > many tables? > > > If you mean blank like '', then if the field is a string, it > > would be normal behavior as is. > > > > Yes, they are strings (varchar). Know the best way to handle it? Can a plpgsql function "walk" the fields of an insert / update and replace every '' with the keyword DEFAULT? I would think that would be the answer. You'd need to be in 7.3 for DEFAULT to work. In 7.2 I'm not sure how you'd set a default on '' though.
did you try what I gave you? It should have puked when you tried to insert a blank field. Robert Fitzpatrick wrote: > Looking for the best way of handling this, would like to do it in the > database and not in my apps. I set a field to 'Not Null', but if a blank > value comes in, the field will accept it as just that, a blank string. > How can avoid this using a trigger or function? I thought about a > function that would check the field and then place a trigger, but that > gets to be a lot of functions and triggers for different fields. Is > there a way to pass a field name to the function? (in plpgsql) > > Or what is the best way to handle this for many different fields in many > tables? > > -- > Robert > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Robert, I use a default value on columns that I define as not null and want particular data to be inserted if no value is supplied. After you create your table a simple alter statement is all you need: alter table table_name alter column column_name set default ' '; Hope this helps Karen Grose Vigilos Inc. -----Original Message----- From: Robert Fitzpatrick [mailto:robert@webtent.com] Sent: Monday, April 28, 2003 11:09 AM To: gearond@cvc.net Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Setting a field to default if blank value > Robert Fitzpatrick wrote: > > Looking for the best way of handling this, would like to do > it in the > > database and not in my apps. I set a field to 'Not Null', but if a > > blank value comes in, the field will accept it as just > that, a blank > > string. How can avoid this using a trigger or function? I thought > > about a function that would check the field and then place > a trigger, > > but that gets to be a lot of functions and triggers for different > > fields. Is there a way to pass a field name to the function? (in > > plpgsql) > > > > Or what is the best way to handle this for many different fields in > > many tables? > If you mean blank like '', then if the field is a string, it > would be normal behavior as is. > Yes, they are strings (varchar). Know the best way to handle it? -- Robert ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly