Change empty string to NULL when column has DATE constraint

Question

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.

If all else fails, you could import to a staging table then insert from there with a case on the insert — JohnHC
– JohnHC, Commented May 22, 2019 at 13:41

404 · Accepted Answer · 2019-05-22 14:19:22Z

3

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)

edited May 22, 2019 at 14:19

answered May 22, 2019 at 13:48

404

8,7522 gold badges33 silver badges53 bronze badges

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

7 Comments

Tytire Recubans Over a year ago

Thanks! your_input_field should be replaced by some_date?

404 Over a year ago

your_input_field is the JSON field value you want to insert in the some_date field in your table, which could be an empty string. Doing a NULLIF on the value will return NULL if the value of the first parameter is equal to the value of the second parameter. Let me know if I misunderstood your question.

Tytire Recubans Over a year ago

Jackpot would be if I could insert a NULL for a list of strings like # - -- ?? etc... the so-called NITS (nothing interesting to say).

404 Over a year ago

Glad to help! :)

404 Over a year ago

@TytireRecubans I've posted an answer on that question, see if that works for you.

|

Tytire Recubans · Accepted Answer · 2019-05-22 14:52:23Z

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.

CREATE TABLE COMPANY(
   ID             VARCHAR,
   NAME           VARCHAR,
   AGE            VARCHAR,
   ADDRESS        VARCHAR,
   DATE           INTEGER
);

INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, DATE)
VALUES (CASE WHEN cast('a' as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE 'a' END,
        CASE WHEN cast('gino' as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE 'gino' END,
        CASE WHEN cast('' as text) IN ('', '#', '-', '--', '??', 'na') THEN NULL ELSE '' END,
        CASE WHEN cast('via figa' as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE 'via figa' END,
        CASE WHEN cast(1 as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE 1  END);

Collectives™ on Stack Overflow

Change empty string to NULL when column has DATE constraint

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related