How to convert a '' to NULL in postgreSQL

Question

I have a postgreSQL table which accepts date in yyyy-mm-dd format and it is not accepting if the incoming date format is ''(no date). There could be some instances when '' gets passed as date. Could anyone help me write a function which checks if the incoming date is '' and then replaces it with NULL and then adds it to the db.

How would you add it to the database if the column is not null? — Mureinik
– Mureinik, Commented Jul 16, 2015 at 7:08
Apologies for not wording it better. I'd updated the text. what i meant was when a null date is passed. — Morpheus
– Morpheus, Commented Jul 16, 2015 at 7:10
This is a tcl command update control_tracker set date='[regsub -all {\s} [string trim [regsub {\{.*} $whens1_12 ""]] "-" ]' — Morpheus
– Morpheus, Commented Jul 16, 2015 at 7:15
Dates don't have "a format". So "accepts date in yyyy-mm-dd forma*" doesn't really make sense. Which data type exactly is that column? — user330315
– user330315, Commented Jul 16, 2015 at 7:20

user330315 · Accepted Answer · 2015-07-16 07:21:14Z

4

Use nullif()

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

Or for an update

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

answered Jul 16, 2015 at 7:21

user330315

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2015-07-16 07:16:18Z

0

You could use a case expression to check for this. I'm using :arg to represent the inputting string - change it according to the programming language you're using:

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

answered Jul 16, 2015 at 7:16

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Collectives™ on Stack Overflow

How to convert a '' to NULL in postgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related