Ok, well, totally went down the rabbit hole on this one, but I did come up with a trigger function that is reusable for multiple tables, and will test multiple columns for empty strings. I figured I’d put it here; maybe it will save someone some time someday
The function is built for Postgresql. The SQLFiddle I was using listed version 9.6, but I think it might be backward compatible to somewhere around 9.0. It does require the hstore extension to be enabled on the database you are using this function on. As far as I know, the extension has been bundled with Postgresql on most distributions for a while now. Enable it by running the following code once:
CREATE EXTENSION hstore;
Add the function to your database by running the following SQL code:
CREATE OR REPLACE FUNCTION empty_str_to_null()
RETURNS TRIGGER AS
* This function takes a list of column names and
* checks the row being inserted/updated for empty strings in those columns.
* If any empty strings are found, those columns will be converted to NULLs
* and the row returned.
-- make sure at least one argument was passed to the function
IF TG_NARGS = 0
RAISE EXCEPTION 'Function empty_str_to_null requires at least one column name to be passed as an argument';
FOR i IN 0..(TG_NARGS-1)
_col_name := quote_ident(TG_ARGV[i]);
EXECUTE format('SELECT ($1).%s::text', _col_name)
-- check to see if the column is an empty string
IF _col_value = ''
-- add this column name to the array of columns to be nulled
cols_to_null = array_append(cols_to_null,_col_name);
-- also add a null value to the nulls_array. the hstore function expects same length arrays
null_array = array_append(null_array, NULL);
-- check to see if any columns were found that need to be nulled
IF array_length(cols_to_null, 1) > 0
-- build hstore object and do the replace
hstore_list = hstore(cols_to_null, null_array);
EXECUTE format('SELECT ($1 #= ''%s'').*', hstore_list)
$$ LANGUAGE plpgsql;
Then create the trigger on your table with code similar to the following:
CREATE TRIGGER demo1
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE empty_str_to_null('txt', 'other_field');
The arguments you supply to the function are the names of the columns that you want to be converted to nulls if the field is an empty string.
To anyone reading, feel free to pick this apart and critique it. I’m sure there’s something I’m doing in there that could be done better.