Clever Geek Handbook

How to convert '' to NULL in postgreSQL

I have a postgreSQL table that accepts a date in yyyy-mm-dd format and does not accept if the format is an incoming date ''

(no date). There may be some cases where it ''

is passed as a date. Can anyone help me write a function that checks if the incoming date is "" and then replaces it with NULL and then adds it to the db.

+3


source to share


2 answers


Use nullif() 

insert into the_table (the_date_column)
values (nullif(?, ''))


Or to update

update the_table
  set the_date_column = nullif(?, '');
+4


source


You can use an expression case

to check this. I am using :arg

to represent the input string - change it according to the programming language you are using:



INSERT INTO mytable
(my_date_col)
VALUES (CASE LENGTH(:arg) WHEN 0 THEN NULL ELSE TO_DATE(:arg, 'yyyy-mm-dd' END)
0


source






More articles:


All Articles