Inserting DEFAULT value into a column when a parameter is NULL

Question

I would like to write a stored procedure like this:

CREATE OR REPLACE FUNCTION my_function(param_1 text, param_2 text DEFAULT NULL::text) RETURNS bigint AS
$$
DECLARE ret bigint;
BEGIN
    INSERT INTO my_table(val_1, val_2) VALUES (param_1, param_2);

    -- do some more stuff

    RETURN ret;
END;
$$
LANGUAGE plpgsql;

However, I would like to use val_2 column's DEFAULT value instead of NULL - if NULL is provided as the param_2 value.

Something like this:

INSERT INTO my_table(val_1, val_2) VALUES (param_1, COALESCE(param_2, DEFAULT));

is obviously wrong, since the INSERT statement specification explicitly states an expression OR DEFAULT can be used, DEFAULT itself is not available in expressions.

I found two solutions myself but I'm not satisfied with them.

Select the DEFAULT value from the information schema and use it in the COALESCE expression.

I'm no expert but it seems like there should be a simpler and more elegant way to do it.

Use INSERT and then UPDATE

Like this:

-- ...
INSERT INTO my_table(val_1) VALUES (param_1)
RETURNING id INTO id_var;

IF (param_2) IS NOT NULL THEN
    UPDATE my_table SET val_2 = param_2 WHERE id = id_var;
END IF;
-- ...

There is however a catch in this solution. The actual table of the production system has some intricate triggers which run on UPDATE statements on this table so I would generally like to avoid using updates if possible.

Generally, I'll possibly stick to the second solution but that would possibly require adding some hacks to the aforementioned triggers. But if there is a way to avoid this - I will be very grateful for pointing it out.

Due to the way that DEFAULT works, I think you're going to need a different INSERT statement for each case. So either choose between two variants using IF param_2 IS NULL THEN ..., or build a dynamic statement and run it via EXECUTE — Nick Barnes
– Nick Barnes, Commented Mar 11, 2016 at 12:22

Gordon Linoff · Accepted Answer · 2016-03-11 12:19:44Z

7

This is a bit long for a comment.

One method would be to declare the column NOT NULL. Inserting the NULL value would generate a constraint violation, which you can catch in the insert using on constraint.

This seems like a correct approach. If the column has a default value, then you probably do not want it to be NULL.

answered Mar 11, 2016 at 12:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2016-03-12 10:58:40Z

4

As param_2 can only be one of null or not null only one of the selects will return a row to be inserted:

with i as (
    insert into my_table (val_1)
    select param_1
    where param_2 is null
)
insert into my_table (val_1, val_2)
select param_1, param_2
where param_2 is not null

If it is necessary to return the inserted values:

with i_null as (
    insert into my_table (val_1)
    select param_1
    where param_2 is null
    returning *
), i_notnull as (
    insert into my_table (val_1, val_2)
    select param_1, param_2
    where param_2 is not null
    returning *
)
select * from i_null
union all
select * from i_notnull

edited Mar 12, 2016 at 10:58

answered Mar 11, 2016 at 16:03

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2016-03-11 14:15:35Z

3

Using dynamic-SQL:

CREATE OR REPLACE FUNCTION my_function(param_1 text, param_2 text DEFAULT NULL::text)
  RETURNS bigint AS
$$
DECLARE ret bigint;
BEGIN

 EXECUTE 'INSERT INTO my_table(val_1, val_2) VALUES (' ||
          quote_literal(param_1) || ',' ||
          CASE WHEN param_2 IS NULL THEN 'DEFAULT' ELSE quote_literal(param_2) END ||
          ')';

 RETURN ret;
END;
$$
LANGUAGE plpgsql;

SqlFiddleDemo

edited Mar 11, 2016 at 14:15

answered Mar 11, 2016 at 13:14

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

Ezequiel Tolnay · Accepted Answer · 2018-03-02 05:59:56Z

1

If insert is as simple as in the question, I'd use two different INSERT statements, since the resulting code is small and readable and fast:

IF param_2 IS NULL THEN
  INSERT INTO my_table(val_1) VALUES (param_1);
ELSE
  INSERT INTO my_table(val_1, val_2) VALUES (param_1, param_2);
END IF;

For inserts with a greater level of complexity, for example with more than one optional parameter is used, I may opt for the dynamic-SQL solution by @lad2025 above.

answered Mar 2, 2018 at 5:59

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Comments

Ben Millwood · Accepted Answer · 2022-08-12 13:30:30Z

0

This is probably excessively heavyweight for your purpose, but it could make sense if there were lots of optional parameters, or lots of rows to be inserted:

BEGIN;
CREATE TEMPORARY TABLE temp_table (LIKE my_table INCLUDING DEFAULTS) ON COMMIT DROP;
ALTER TABLE temp_table ALTER COLUMN val_2 DROP NOT NULL; -- if necessary
INSERT INTO temp_table(val_1, val_2) VALUES (param_1, param_2);
UPDATE temp_table SET val_2 = DEFAULT WHERE val_2 IS NULL;
INSERT INTO my_table SELECT * FROM temp_table;
COMMIT;

answered Aug 12, 2022 at 13:30

Ben Millwood

7,08129 silver badges47 bronze badges

Collectives™ on Stack Overflow

Inserting DEFAULT value into a column when a parameter is NULL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related