I'm trying to run an insert query where it will insert to a table if the data has all parameters; else, if there are missing parameters, insert nulls as values.
An example would be some records will not have last_delivery nor volume_rate parameters. So how can I insert nulls instead into the table?
CREATE OR REPLACE FUNCTION table(
site_id integer,
position_id integer,
tank_level numeric,
tank_volume numeric,
last_delivery numeric,
volume_rate numeric
) RETURNS void
LANGUAGE sql
PARALLEL SAFE LEAKPROOF
AS $function$
INSERT INTO table(
-- column names
site_id,
position_id,
tank_level,
tank_volume,
last_delivery,
volume_rate
) VALUES (
-- arguments to this function
site_id::INTEGER,
position_id::INTEGER,
tank_level::NUMERIC,
tank_volume::NUMERIC,
last_delivery::NUMERIC,
volume_rate::NUMERIC)
$function$;
I have tried adding CASE
statements as follows but no dice.
INSERT INTO table (
-- column names
site_id,
position_id,
tank_level,
tank_volume,
last_delivery,
volume_rate
) VALUES (
-- arguments to this function
site_id::INTEGER,
position_id::INTEGER,
tank_level::NUMERIC,
tank_volume::NUMERIC,
case
when last_delivery::numeric is null then 0
else last_delivery::numeric
end,
case
when volume_rate::NUMERIC is null then 0
else volume_rate::NUMERIC
end
)