Change empty string to NULL when column has DATE constraint

This might be impossible but I was wondering if someone more experienced knew if this is possible to do in postgresql.

I have a column in my create statement

CREATE table IF NOT EXISTS (other cols, some_date DATE, other cols);

When I extract the json object from the API, there might actually be an empty string ” instead of a empty cell. Which of course gives me the following error psycopg2.errors.InvalidDatetimeFormat: invalid input syntax for type date: ""

The solution would simply to change the constraint to VARCHAR, but i was wondering if there was some way in the CREATE TABLE or INSERT statements to say the following pseudo code: if empty string insert NULL.

Answer

Use NULLIF in your INSERT statement:

INSERT INTO your_table (cols..., some_date) VALUES (..., NULLIF(your_input_field, ''))

If you want to insert NULL if the value in question is any of a number of values, it may be easiest to use a CASE statement:

INSERT INTO your_table (cols..., some_date)
VALUES (..., CASE WHEN your_input_field IN ('', '#', '-', '--', '??') THEN NULL ELSE your_input_field END)

Could do the same with an array as well, if that’s easier:

INSERT INTO your_table (cols..., some_date)
VALUES (..., CASE WHEN your_input_field = ANY('{"",#,-,--,??}'::TEXT[]) THEN NULL ELSE your_input_field END)