How to disambiguate a plpgsql variable name in a ON CONFLICT clause?

Question

Given this table:

create table test (
    name text primary key
);

I need to write a plpgsql function with a variable name that collides with the primary key name, which I must use in a on conflict clause:

create or replace function func(
    name text                               -- this variable name...
) returns void language plpgsql as 
$$
begin 
    insert into test (name) values (name) 
    on conflict (name) do update            -- ...conflicts with this line
    set name = func.name; 
end; 
$$;

This compiles, but then throws an ambiguous column reference:

select * from func('one');
ERROR:  column reference "name" is ambiguous
LINE 2:     on conflict (name) do update 
                        ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  insert into test (name) values (name) 
    on conflict (name) do update 
    set name = func.name
CONTEXT:  PL/pgSQL function func(text) line 3 at SQL statement

I tried specifying the full column name as on conflict (test.name) which does not compile, or ((test.name)) which compiles:

create or replace function func(
    name text
) returns void language plpgsql as 
$$
begin 
    insert into test (name) values (name) 
    on conflict ((test.name)) do            -- this fails too
    update set name = func.name; 
end; 
$$;

But it fails as well:

select * from func('two');
ERROR:  invalid reference to FROM-clause entry for table "test"
LINE 2:     on conflict ((test.name)) do 
                          ^
HINT:  There is an entry for table "test", but it cannot be referenced from this part of the query.
QUERY:  insert into test (name) values (name) 
    on conflict ((test.name)) do 
    update set name = func.name
CONTEXT:  PL/pgSQL function func(text) line 3 at SQL statement

Is there a solution?

Edit: I found a workaround:

on conflict on constraint test_pkey do update

where test_pkey is the table name plus _pkey. I don't know how reliable this is though. I'd still like to specify the column name instead.

Vao Tsun · Accepted Answer · 2017-12-06T18:50:04.943

to start with, name is a bad name for both variable and attribute. When you have both, code won't look good. with that in mind, you can "prefix" variable with labeled block (in example below <<fn>>``), and setvariable_conflict` to give preference to column name, see code below:

t=# create or replace function func(
    name text
) returns void language plpgsql as
$$
#variable_conflict use_column
<<fn>>
declare name text :='blah';
begin
    insert into test (name) values (name)
    on conflict (name) do            -- this no longer fails
    update set name = fn.name;
end;
$$;
t=# insert into test select 'b';
INSERT 0 1
Time: 8.076 ms
t=# select func('b');
 func
------

(1 row)

Time: 6.117 ms
t=# select * from test;
 name
------
 b
 blah
(2 rows)

https://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

By default, PL/pgSQL will report an error if a name in a SQL statement could refer to either a variable or a table column. You can fix such a problem by renaming the variable or column, or by qualifying the ambiguous reference, or by telling PL/pgSQL which interpretation to prefer.

and further - basically the whole link is about it.

And yet - after demonstrating how particular task this can be easily done with plpgsql, I still quote namual:

The simplest solution is to rename the variable or column. A common coding rule is to use a different naming convention for PL/pgSQL variables than you use for column names. For example, if you consistently name function variables v_something while none of your column names start with v_, no conflicts will occur.

@IMSoP I elaborated answer a bit - does look more comprehendible ? — Vao Tsun, Dec 06 '17 at 16:59
Great! Out of interest, is the extra bracket in `((name))` needed once you've applied the `#variable_conflict` rule, or would `(name)` do as well? Reading the manual, it looks like the extra brackets specifies an "index expression" rather than "index column name", and I'm wondering if this makes any difference to the name resolution. — IMSoP, Dec 06 '17 at 17:56
sorry - just copy paste from OP. nothing specific for vars substitute. edited to be single brackets — Vao Tsun, Dec 06 '17 at 18:47

IMSoP · Answer 2 · 2017-12-06T17:56:17.950

The ON CONFLICT... syntax (as documented here) uses a unique constraint to determine if the row conflicts. You can specify this unique constraint either by listing the columns it contains (at which point Postgres "infers" the correct index to use) or by naming the constraint directly.

In your case, the unique constraint being used is the primary key constraint implicitly created during your CREATE TABLE statement. This will have a name given to it by the DBMS, unless you specify one directly; so you will need to either look up the name the DBMS has given it (and be aware that this may change if you recreate the schema later), or name it explicitly when you create the table using the syntax CONSTRAINT pk_some_name PRIMARY KEY.

You would then specify the clause as ON CONFLICT ON CONSTRAINT pk_some_name DO ... (note no brackets around the constraint name).

(Alternatively, of course, you could change your function to use an unambiguous parameter name; personally, I think it's good practice to use a prefix like p_ or in_ rather than handling conflicts on a case-by-case basis.)

How to disambiguate a plpgsql variable name in a ON CONFLICT clause?

2 Answers2

Linked