plpgsql doesn't know that it shouldn't try to resolve references inside of an ON CONFLICT phrase:
~@REMC02PRYM9G8WP/97243# \set VERBOSITY verbose ~@REMC02PRYM9G8WP/97243# CREATE TEMP TABLE testt(a int primary key); CREATE TABLE ~@REMC02PRYM9G8WP/97243# CREATE OR REPLACE FUNCTION pg_temp.testf(a int) RETURNS void LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING; END $body$; CREATE FUNCTION ~@REMC02PRYM9G8WP/97243# select pg_temp.testf(1); ERROR: 42702: column reference "a" is ambiguous LINE 1: INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHI... ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING CONTEXT: PL/pgSQL function pg_temp_3.testf(integer) line 3 at SQL statement LOCATION: plpgsql_post_column_ref, pl_comp.c:1077 ~@REMC02PRYM9G8WP/97243# CREATE OR REPLACE FUNCTION pg_temp.testf(a int) RETURNS void LANGUAGE plpgsql AS $body$ BEGIN INSERT INTO testt(a) SELECT testf.a ON CONFLICT (testf.a) DO NOTHING; END $body$; ERROR: 42601: syntax error at or near ")" LINE 3: ... INTO testt(a) SELECT testf.a ON CONFLICT (testf.a) DO NOTHI... ^ LOCATION: scanner_yyerror, scan.l:1087 ~@REMC02PRYM9G8WP/97243#
Not sure if it'd be easier to fix plpgsql or to broaden the general grammar to allow qualified references in ON CONFLICT. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
This is not a bug. It's a naming conflict due to having a parameter and a column with the same name (a).
You can use different name for the parameter or tell Postgres how to resolve such conflicts:
CREATE OR REPLACE FUNCTION pg_temp.testf(a int) RETURNS void LANGUAGE plpgsql AS $body$ #variable_conflict use_column BEGIN INSERT INTO testt(a) SELECT testf.a ON CONFLICT (a) DO NOTHING; END $body$;
