Can I INSERT INTO IF NOT NULL

Queries and Resources
1

I'm trying to insert one row for each item in a long form, but only if the value in the form is not null.

So far, I've used a SQL query to insert a row for every item in the form, and it just creates a null row when the value in the form is null, but now I am adding even more items to the form and this won't be sustainable any more, so I would like to be able to only add a row when the value is not null.

Pasted below is the insert into statement I'm using. For concision, this is for two values from the form, and there are about 50 others, so it's a very long insert statement. I would like to be able to have logic in the query that would insert values only if the value from the form is not null. So for the first value, 'if textArea.value IS NOT NULL', and for the second value 'if textArea2.value IS NOT NULL', and so on and so forth for the other ~50 values.

insert into table (name, date, location, feature, feature2, rating) VALUES ({{ name.value }}, {{date.value}}, {{location.value}}, {{feature.value}}, {{textArea.label}}, {{textArea.value}}), ({{ name.value }}, {{date.value}}, {{location.value}}, {{feature.value}}, {{textArea2.label}}, {{textArea2.value}})

I'm open to it if there is a more graceful solution altogether too, like using the GUI but I wasn't able to figure out how to make that work for my use case.

1 Like
2

you could take this solution from SO and either use it as is and loop through each value in retool or modify it to send it all values and let the database function handle checking and inserting

CREATE OR REPLACE FUNCTION app.some_function(my_param integer)
RETURNS void
LANGUAGE sql
VOLATILE
AS
$BODY$

INSERT INTO app.myTable(something)
select my_param
where my_param IS NOT NULL
and my_param <> ''
ON CONFLICT(something) DO
UPDATE
SET --someConflictLogic;

$BODY$;

1 Like
3

You could add a validation requirement on the form for the critical field or fields. That would result in the form not submitting if they key field is blank (or a custom requirement, if you need to go there).

2 Likes