(SOLVED) [BUG] Optional (null) values on Insert Action

Get Help
1

I'm trying to use Actions to make an Insert into my table, but I'm having problem with the optional columns, here is my query:

INSERT INTO aurelium.plat_dict (
    tag,
    record_type,
    translation_from,
    translation_to_pt_br,
    translation_to_en_us,
    translation_to_es_es,
    translation_to,
    display_order
)   
VALUES (
    'audit',
    {{record_type}},
    {{translation_from}},
    {{translation_to_pt_br}},
    [[ {{translation_to_en_us}} --]]NULL,
    [[ {{translation_to_es_es}} --]]NULL,
    [[ {{translation_to}} --]]NULL,
    [[ {{display_order}} --]]NULL
);

It works when you don't pass a value on any of the optional columns (except the display_order), when you pass a value there, it generates the following errors:

Error executing Action: Error executing write query: ERROR: syntax error at or near "NULL" Position: 269
Error executing Action: Error executing write query: ERROR: syntax error at or near "$5" Position: 270

I've tried to use

  • COALESCE(NULL, {{variable}}) and
  • CASE WHEN {{variable}} IS NULL THEN NULL ELSE {{variable}} END

But got a new error:

Error executing Action: Error preprocessing query in metabase.query_processor.preprocess$ensure_legacy$fn__65286@51654b15: Cannot run the query: missing required parameters: #{"translation_to_en_us" "translation_to_es_es" "translation_to" "display_order"}

Is this a metabase limitation or is there another option I should be using?

PS: I'm not an admin user, so I can't share any logs.

2

You should be passing the values in the UI section on the right

3

I want it to be optional, i set the Required to false on the UI, but still doesn't work as intended

image
image1565×869 56.4 KB

4
5

Even when doing the "Intended way", normal query but set to optional, still get errors

image
image637×846 22.6 KB

image
image1545×851 47.6 KB

Isn't it supposed to set to Null if I use (optional) and pass no value?

6

I solved it, it's really strange that changing the comma position made it work.
Maybe explain it in the custom actions INSERT doc example?

INSERT INTO aurelium.plat_dict (
    tag,
    record_type,
    translation_from,
    translation_to_pt_br,
    translation_to_en_us,
    translation_to_es_es,
    translation_to,
    display_order
)   
VALUES (
    'audit',
    {{record_type}},
    {{translation_from}},
    {{translation_to_pt_br}}
    , [[ {{translation_to_en_us}} --]]NULL
    , [[ {{translation_to_es_es}} --]]NULL
    , [[ {{translation_to}} --]]NULL
    , [[ {{display_order}} --]]NULL
);

, [[ {{variable}} --]]NULL

Any other variation of this implementation breaks, it needs to be exactly like above.

Only other INSERT example with optional I found