Question 343825 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: field names in "on conflict (…)" inside the function results table

If there is a field in the description of the resulting table in the function declaration (for example, field1) that occurs in parentheses "insert … on conflict(field1)", then an error occurs when executing the function:

SQL Error [42702]: ERROR: column reference "field1" is ambiguous

In order not to describe the problem for a long time and difficult, I immediately give an example of a ready-made test code with strange behavior:

Creating a table into which the row will be inserted :

drop table if exists testing_table;
create TABLE testing_table (
  -- Keep in mind the name of this field
  field1 int4
, s1 text
, constraint pk_testing_table_field1 PRIMARY key (field1)
);

Creating a function that inserts a row into a table

-- drop function if exists insert_testing_table;
create or replace function insert_testing_table(p_field1 int4, p_s1 text)
returns table 
(
  -- Keep in mind the name of this field
  field1 int4
  , s1 text
)
LANGUAGE plpgsql AS $function$
begin 

insert into testing_table as r (field1, s1) values (p_field1, p_s1)
on conflict (
-- It is this place that is the conflicting name that causes the error
field1
)
do update set s1 = p_s1 where r.field1 = p_field1;

return query select t.* from testing_table t where t.field1 = p_field1;

end $function$;

Calling a function:

select * from insert_testing_table(1, 'aaa');

This call will result in an error:

SQL Error [42702]: ERROR: column reference "field1" is ambiguous

You cannot specify either the alias, the name of the table, or the name of the constrain in parentheses of the expression "on conflict(…)", only the direct names of the fields of the table.

What does it mean: "is ambiguous"?
Which column of which table does the field1 column conflict with in the "insert on conflict do update" statement?

The error persists if I rename the field in the "return query select…" expression.

But if I rename the "field1" field in the description of the resulting table, the function will run without error.

How can I specify the name of the field in parentheses of the expression "on conflict(…)" to eliminate the error?

I understand that I can use:

ON CONFLICT ON CONSTRAINT constraint_name

But I want to sort out the abnormal situation with specifying the field in "on conflict(…)" , the same field that is in the description of the returning table.
What kind of strangeness is this?

Tags:

2

Answers


  1. 0

    It is strange that PL/pgSQL tries to match a variable here; perhaps that’s a bug.

    Your options at this point are:

    • give the column in the RETURNS TABLE clause a different, non-conflicting name

    • use dynamic SQL:

      EXECUTE 'INSERT INTO testing_table as r (field1, s1)
         VALUES ($1, $2)
         ON CONFLICT (field1) DO UPDATE
         SET s1 = $2'
USING p_field1, p_s1;

    • put the INSERT statement into yet another function with different parameter names and call that

    Login or Signup to reply.
  2. 0

    An alternative is to totally get rid of all variables other than the parameters ( p_…). Postgres automatically creates a type definition when a table is created with the same name as the table itself. You can use this as the return; you function declaration then essentially becomes returns <table_name>. Further when a conflict occurs Postgres generates an interval tuple with the declaration of the row being inserted. This tuple is referred to as excluded. with the same column names as the actual table. Also Postgres knows exactly what row it was processing so there is no need to specify the where.
    Finally, in this case at least, this can be accomplished with a single statement and can be put into a SQL function – no pgplsql needed. So: (see demo here)

    create or replace function insert_testing_table(p_field1 int4, p_s1 text)
  returns testing_table
  language sql 
as $$
    insert into testing_table(field1, s1) 
         values (p_field1, p_s1)
         on conflict (field1)
         do update 
               set s1 = excluded.s1
         returning *;
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search