Postgres INSERT IF parameter is not null

Question

I have a function which takes a parameter, and I want to only insert that param into a table if it's not null. I'm trying something like this:

CREATE OR REPLACE FUNCTION app.some_function(
    my_param integer)
    RETURNS void
    LANGUAGE 'sql'

    COST 100
    VOLATILE 
AS $BODY$

INSERT INTO app.myTable(something)
    VALUES(my_param)
    ON CONFLICT(something) DO
    UPDATE
    SET --someConflictLogic
    WHERE my_param <> '' AND my_param IS NOT NULL;

$BODY$;

I was hoping the WHERE clause here would cover me, but it's not the case. When I call this function with my_param as NULL, I get:

null value in column "something" violates not-null constraint

So it looks like it's still trying to insert it. How can I correct this condition?

Kaushik Nayak · Accepted Answer · 2018-10-16 13:26:07Z

3

You may use IF..THEN and change to plpgsql function

 CREATE OR REPLACE FUNCTION app.some_function(
        my_param integer)
        RETURNS void
        LANGUAGE plpgsql

        COST 100
        VOLATILE 
    AS $BODY$

 IF NULLIF(my_param,'') IS NOT NULL  THEN       

    INSERT INTO app.myTable(something)
        VALUES(my_param)
        ON CONFLICT(something) DO
        UPDATE
        SET --someConflictLogic;
 END IF;   
    $BODY$;

edited Oct 16, 2018 at 13:26

answered Oct 16, 2018 at 13:23

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

user330315 · Accepted Answer · 2018-10-16 13:20:59Z

2

You can turn the insert ... values () into aninsert ... select` with a where clause attached:

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$;

The where clause you used only applies to the DO UPDATE part, not the "primary" insert part.

Unrelated, but: the name of the function language is an identifier. It should be quoted.

answered Oct 16, 2018 at 13:20

user330315

Collectives™ on Stack Overflow

Postgres INSERT IF parameter is not null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related