CODEWITHSUNDEEP
postgresql
leonardorame
leonardorame

Reputation: 1161

Handling empty integers

I have this situation where an application some times is passing empty values to an integer parameter, thus getting syntax error at or near ','.

Is there a way, from the stored procedure side to handle this?.

An easy way to reproduce this, is for example doing this:

select (''::integer);

This obviously returns:

ERROR:  syntax error at or near ":"

That is more or less the same error the stored procedure is returning in the sample case.

P.S.: the server is PostgreSql 9.5.

Upvotes: 0

Views: 91

Answers (1)

klin
klin

Reputation: 121604

Use nullif().

with the_data(str) as (
values 
    ('1'), 
    ('2'), 
    ('')
)

select nullif(str, '')::integer as value
from the_data;

  value 
--------
      1
      2
 <null>    
(3 rows)

Upvotes: 2

Related Questions