BUG #14343: UPSERT (ON CONFLICT) doesn't check ON CONFLICT constraint first

The following bug has been logged on the website:

Bug reference: 14343
Logged by: Reyes Ponce
Email address: reyes(dot)r(dot)ponce(at)gmail(dot)com
PostgreSQL version: 9.5.2
Operating system: RHEL 4.8.2-16, 64-bit
Description:

-- Create a table with a key, one not null column, and one nullable
column.

CREATE TABLE public.MyTable
(
MY_ID integer primary key NOT NULL,
COL1 integer NOT NULL,
COL2 integer,
CRETN_TS timestamp with time zone NOT NULL,
CRETN_USER_ID varchar NOT NULL,
UPDT_TS timestamp with time zone ,
UPDT_USER_ID varchar
);

-- Create a function to upsert whatever is passed in.

CREATE OR REPLACE FUNCTION public.upsert_mytable(
i_my_id integer,
i_col1val integer DEFAULT NULL::integer,
i_col2val integer DEFAULT NULL::integer
)
RETURNS json AS
$BODY$
DECLARE
get_rows json;
beginSql text :=
'INSERT INTO public.MyTable(
MY_ID, COL1, COL2, CRETN_TS, CRETN_USER_ID, UPDT_TS,
UPDT_USER_ID)
VALUES ($1, $2, $3, NOW(), current_user, NOW(), current_user)
ON CONFLICT(MY_ID)
DO UPDATE SET UPDT_TS = NOW(), UPDT_USER_ID = current_user, ';
comma text := '';
endSql text := '';
sql text := '';
BEGIN

-- Validate input parameters - MUST SPECIFY PK

IF ( i_my_id is NULL OR i_my_id = '0') THEN
Raise Exception 'INVALID INPUT: Id cannot be null' using errcode =
'3';
END IF;

/* Create middle SQL (the fields to update).

After we see the first non-null parameter, set the comma to a comma
instead of blank...

The $ variables line up with the order in the "EXECUTE sql USING"
statement below, not in order in the parameter list above.
*/
IF (i_col1val is not NULL) THEN
endSql := endSql || comma || 'COL1 = $2';
comma := ', ';
END IF;

IF (i_col2val is not NULL) THEN
endSql := endSql || comma || 'COL2 = $3';
comma := ', ';
END IF;

-- raise NOTICE '%', beginSql; /* DEBUGGING CODE - Outputs message in
plAdmin messages tab. */
-- raise NOTICE '%', endSql; /* DEBUGGING CODE - Outputs message in
plAdmin messages tab. */

sql := beginSql || endSql;

-- raise NOTICE '%', sql; /* DEBUGGING CODE - Outputs message in plAdmin
messages tab. */

/* The $ variables line up with the order in the USING clause below, not in
order in the parameter list. */
EXECUTE sql
USING i_my_id, i_col1val, i_col2val;

-- Return json.

select row_to_json(f) INTO get_rows
from (
SELECT MY_ID, COL1, COL2, cretn_ts, cretn_user_id, updt_ts,
updt_user_id
FROM public.MyTable
WHERE MY_ID = i_my_id) f;

RETURN get_rows;

END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

-- Call the SP for initial insert (all columns). Works fine.
SELECT public.upsert_mytable(
1,
2,
3
);

-- Call the SP for update of the nullable column. Error because not checking
ON CONFLICT condition first.
SELECT public.upsert_mytable(
1,
NULL,
5
);

/*
ERROR: null value in column "col1" violates not-null constraint
DETAIL: Failing row contains (1, null, 5, 2016-09-27 17:32:51.054896+00,
pl_mstr_usr, 2016-09-27 17:32:51.054896+00, pl_mstr_usr).
CONTEXT: SQL statement "INSERT INTO public.MyTable(
MY_ID, COL1, COL2, CRETN_TS, CRETN_USER_ID, UPDT_TS,
UPDT_USER_ID)
VALUES ($1, $2, $3, NOW(), current_user, NOW(), current_user)
ON CONFLICT(MY_ID)
DO UPDATE SET UPDT_TS = NOW(), UPDT_USER_ID = current_user, COL2 = $3"
PL/pgSQL function upsert_mytable(integer,integer,integer) line 46 at
EXECUTE

********** Error **********

ERROR: null value in column "col1" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (1, null, 5, 2016-09-27 17:32:51.054896+00,
pl_mstr_usr, 2016-09-27 17:32:51.054896+00, pl_mstr_usr).
Context: SQL statement "INSERT INTO public.MyTable(
MY_ID, COL1, COL2, CRETN_TS, CRETN_USER_ID, UPDT_TS,
UPDT_USER_ID)
VALUES ($1, $2, $3, NOW(), current_user, NOW(), current_user)
ON CONFLICT(MY_ID)
DO UPDATE SET UPDT_TS = NOW(), UPDT_USER_ID = current_user, COL2 = $3"
PL/pgSQL function upsert_mytable(integer,integer,integer) line 46 at
EXECUTE

-----------------------------------------------------------------------

ON CONFLICT should have been checked before the attempt to insert!
If i implement as CTE instead of using the new syntax it works fine.
Not checking ON CONFLICT condition first makes the new syntax applicable
to
fewer scenarios than it should be able to be used for. It's useless for this
particular use case...

*/