php - How to convert empty to null in PostgreSQL?

Question

I have some columns type int, but value is empty. So I want to convert empty to null when I insert to database. I use code:

function toDB($string) {
    if ($string == '' || $string == "''") {
        return 'null';
    } else {
        return "'$string'";
    }
}

//age,month,year is type integer.
$name="Veo ve";
$age='10';
$month='';
$year='';
    $query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})
 $db->setQuery($query);
 $result= $db->query();

But it show error:

 pg_query(): Query failed: ERROR: syntax error at or near ";" LINE 153: {toDB(10)}, ^ in...

Why?

Answer 1

There is the NULLIF() function:

SELECT NULLIF(var, '');

If var holds the value in $2, you get NULL instead.
In the example I replace the empty string: '' with NULL.

There is no empty string for the type integer. Just not possible. Since NULLIF() cannot switch the data type, you have to sanitize your input in PHP.

If you did not define a column default, you can also just omit the column in the INSERT command and it will be filled with NULL (which is the default DEFAULT).

Check if the parameter is empty in PHP and don't include the column in the INSERT command if it is.

Or use the PHP literal NULL instead like Quassnoi demonstrates here.

The rest only makes sense for string types

To make absolutely sure, nobody can enter an empty string add a CHECK constraint to the table:

ALTER TABLE tr_view
ADD CONSTRAINT tr_view_age_not_empty CHECK (age <> '');

To avoid exceptions caused by this, you could add a trigger that fixes input automatically:

CREATE OR REPLACE FUNCTION trg_tr_view_avoid_empty()
  RETURNS trigger AS
$func$
BEGIN
   IF NEW.age = '' THEN
      NEW.age := NULL;
   END IF;

   IF NEW.month = '' THEN
      NEW.month := NULL;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql

CREATE TRIGGER tr_view_avoid_empty
BEFORE INSERT OR UPDATE ON tr_view
FOR EACH ROW
WHEN (NEW.age = '' OR NEW.month = '')
EXECUTE PROCEDURE trg_tr_view_avoid_empty();