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 E

Solution 1:

Use NULLIF in your INSERT statement:

INSERTINTO 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:

INSERTINTO your_table (cols..., some_date)
VALUES (..., CASEWHEN your_input_field IN ('', '#', '-', '--', '??') THENNULLELSE your_input_field END)

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

INSERTINTO your_table (cols..., some_date)
VALUES (..., CASEWHEN your_input_field =ANY('{"",#,-,--,??}'::TEXT[]) THENNULLELSE your_input_field END)

Solution 2:

Here's an example of the solution posted by 404 that checks what we are trying to insert in our db is a NITS (nothing interesting to say), and replace it with NULL.

CREATETABLE COMPANY(
   ID             VARCHAR,
   NAME           VARCHAR,
   AGE            VARCHAR,
   ADDRESS        VARCHAR,
   DATEINTEGER
);

INSERTINTO COMPANY (ID, NAME, AGE, ADDRESS, DATE)
VALUES (CASEWHENcast('a'as text) IN ('', '#', '-', '--', '??') THENNULLELSE'a'END,
        CASEWHENcast('gino'as text) IN ('', '#', '-', '--', '??') THENNULLELSE'gino'END,
        CASEWHENcast(''as text) IN ('', '#', '-', '--', '??', 'na') THENNULLELSE''END,
        CASEWHENcast('via figa'as text) IN ('', '#', '-', '--', '??') THENNULLELSE'via figa'END,
        CASEWHENcast(1as text) IN ('', '#', '-', '--', '??') THENNULLELSE1END);