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?

You cannot insert '' into an int, only an integer or a null. — Scott Marlowe, Dec 27 '12 at 04:37

Erwin Brandstetter · Answer 1 · 2022-04-09T03:09:58.137

There is the NULLIF() function:

SELECT NULLIF(var, '');

If var equals the 2nd parameter, you get NULL instead.
The example replaces the empty string '' with NULL.

There is no "empty string" for the type integer. Both parameters must be of compatible type, so 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
  LANGUAGE plpgsql 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$;

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();

I know that thank you comments are discouraged, but I'll go ahead and say thank you. — Benjamin Crouzier, Jan 06 '17 at 15:56

score 9 · Accepted Answer · answered Dec 26 '12 at 05:22

While Erwin's answer about NULLIF is awesome, it doesn't address your syntax error.

Let's take a look at the query:

$query="Insert Into tr_view(name,age,month,year) values ({toDB($name)},{toDB($age)},{toDB($month)},{toDB($year)})

Earlier you defined a function called toDB. Unfortunately the syntax you are using here is not how to call a function from within a double-quoted string, so the curlies and toDB( bits are still being passed through. There are two alternatives:

Concatenation using .:

$query='insert Into tr_view(name,age,month,year) values (' . toDB($name) . ',' . toDB($age) . ',' . toDB($month) . ',' . toDB($year) . ')')

You can interpolate a callable variable into a double-quoted string thusly:

$fn = 'toDB';
$query="Insert Into tr_view(name,age,month,year) values ({$fn($name)},{$fn($age)},{$fn($month)},{$fn($year)})";

The first is clear and sane, the second is vague to the unfamiliar and downright insane.

However, you still should not be assembling input like this. You still may be vulnerable to SQL injection attacks. You should be using prepared statements with parameterized placeholders.

The Postgres extension uses pg_prepare for this. They have the distinct advantage of, say, allowing you to pass a PHP null instead of having to worry about all of that null-detection and quoting.

If you insist on keeping toDB as-is, consider adding one of the pg_escape_ functions, like pg_escape_string, to the thing that builds quoted strings.

How to convert empty to null in PostgreSQL?

2 Answers2

The rest only makes sense for string types

Linked